为每个 Job_ID 返回一行，并包含以下几列:
•J 对象ID
•以年为单位的平均女性服务长度(以年为单位)，四舍五入为一年的十分之一
•该工作的平均女性薪水
•以年为单位的平均男性服务长度(以年为单位)，四舍五入为一年的十分之一
•该工作的平均男性工资
•该工作的男性和女性平均工资差异，以正数表示女性平均工资较高，而负数表示男性平均工资较高。
表
员工
Name Null Type
-------------- -------- ------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
EMAIL NOT NULL VARCHAR2(25)
PHONE_NUMBER VARCHAR2(20)
HIRE_DATE NOT NULL DATE
JOB_ID NOT NULL VARCHAR2(10)
SALARY NUMBER(8,2)
COMMISSION_PCT NUMBER(2,2)
MANAGER_ID NUMBER(6)
DEPARTMENT_ID NUMBER(4)
GENDER CHAR(1)
到目前为止我所拥有的，但这将为每个job_id返回一个M＆F行，我需要M＆F列
SELECT gender, job_id, ROUND(AVG(salary),0) as avg_job_salary,
(SELECT ROUND(AVG(salary),0)
FROM hr.employees
WHERE gender = 'M') AS avg_m_salary, (SELECT ROUND(AVG(salary),0)
FROM hr.employees
WHERE gender = 'F') AS avg_f_salary,
ROUND(AVG(days_of_svc/365),1) AS avg_years_svc
FROM (SELECT job_id, salary, gender, (SYSDATE-hire_date) AS days_of_svc
FROM hr.employees)
GROUP BY job_id,gender
ORDER BY job_id, gender;
或版本2
SELECT gender, job_id, ROUND(AVG(salary),0) as avg_job_salary,
((SELECT ROUND(AVG(salary),0)
FROM hr.employees
WHERE gender = 'F') - (SELECT ROUND(AVG(salary),0)
FROM hr.employees
WHERE gender = 'M')) as diff,
ROUND(AVG(days_of_svc/365),1) AS avg_years_svc
FROM (SELECT job_id, salary, gender, (SYSDATE-hire_date) AS days_of_svc
FROM hr.employees)
GROUP BY job_id,gender
ORDER BY job_id, gender;
样本预期结果行
JOB_ID F_AVG_LENGTH F_AVG_SAL M_AVG_LENGTH M_AVG_SAL DIFFERENCE
------ ------------ --------- ------------ --------- ----------
MAILCLERK 24.1 48000 23.4 47000 1000
CASHIER 4.6 12000 4.4 13500 -1500
最佳答案
在我的机器上，我在架构中复制了HR.EMPLOYEES，并将其命名为克隆HR_EMPLOYEES。然后，我在Oracle副本上添加了GENDER列，因为HR.EMPLOYEES表中没有GENDER列。我用我的最佳猜测填充了该列，仅用于测试。
在Oracle 11中，您可以使用PIVOT操作，这使工作更加容易。我将平均任期除以365.25，因此它以年而不是天来表示。请注意，有许多工作中没有男性或女性，因此有很多NULL结果。我假设您也希望它们显示-否则可以将它们忽略。
select job_id, round(F_AVG_TENURE_D/365.25, 1) as f_avg_length,
round(F_AVG_SALARY) as f_avg_salary,
round(M_AVG_TENURE_D/365.25, 1) as m_avg_length,
round(M_AVG_SALARY) as m_avg_salary,
round(F_AVG_SALARY - M_AVG_SALARY) as avg_sal_diff
from (
select job_id, gender, sysdate - hire_date as tenure, salary
from hr_employees
)
pivot (avg(tenure) as avg_tenure_d, avg(salary) as avg_salary
for gender in ('F' as F, 'M' as M))
order by avg_sal_diff desc nulls last, job_id -- ORDER BY is optional
;
输出:
JOB_ID F_AVG_LENGTH F_AVG_SALARY M_AVG_LENGTH M_AVG_SALARY AVG_SAL_DIFF
---------- ------------ ------------ ------------ ------------ ------------
SH_CLERK 11.2 3511 9.9 2973 538
ST_MAN 12.3 7467 10.3 7000 467
ST_CLERK 10.5 2883 10.8 2743 140
PU_CLERK 11.6 2833 10 2700 133
AD_VP 11.1 17000 15.8 17000 0
SA_REP 10.3 8244 10.6 8471 -228
SA_MAN 10.3 12000 10.9 12333 -333
IT_PROG 10.2 4500 10.5 6600 -2100
AC_ACCOUNT 14.4 8300
AC_MGR 14.4 12008
AD_ASST 13.1 4400
AD_PRES 13.4 24000
FI_ACCOUNT 11.2 7920
FI_MGR 14.2 12008
HR_REP 14.4 6500
MK_MAN 12.7 13000
MK_REP 11.2 6000
PR_REP 14.4 10000
PU_MAN 13.9 11000
19 rows selected.