SQL查询,可以显示所有员工的列表,包括他们的姓名,职位,工资及其工资类别差异。如果员工的工资高于(表示+)或低于(表示-)该职位的平均工资集(参见min_salary和max_salary),后者将给出每个员工的信息。
SELECT
e.employee_id,
e.first_name,
e.last_name,
j.job_title,
e.salary,
CASE WHEN salary>AVG(salary) THEN max_salary - min_salary END AS salary_class_difference
FROM hr.employees e, hr.jobs j
WHERE e.job_id=j.job_id;
这是我能想出的最好的办法了。
Sample数据库
SELECT
e.employee_id,
e.first_name,
e.last_name,
j.job_title,
e.salary,
CASE WHEN salary>(select AVG(salary) from hr.employees)
THEN (select max(salary) from hr.employees)-(select min(salary)from
hr.employees)) END AS salary_class_difference
FROM hr.employees e, hr.jobs j
WHERE e.job_id=j.job_id;