员工表dept_emp:
薪水表salaries:
求获取所有部门中员工薪水最高的相关信息,给出dept_no, emp_no以及其对应的salary,按照部门编号升序排列。
1、错误做法
select dept_no,emp_no,max(salary) as maxSalary
from
(select d.*,s.salary from dept_emp d left join salaries s
on d.emp_no=s.emp_no) n
group by dept_no
order by dept_no;
返回结果:
分析:通过常规的聚合函数获取salary时,无法正确获取对应的员工编号。d004部门最高工资对应的员工编号应该是10004而不是10003.
emp_no是非聚合字段,mysql会随机选择一条,并非max_salary对应的emp_no
2、正确做法
法1:
SELECT D.dept_no,D.emp_no,D.salary
FROM(
SELECT
DENSE_RANK() OVER (PARTITION BY C.dept_no ORDER BY C.salary DESC) AS raking,
C.dept_no,
C.emp_no,
C.salary
FROM (
SELECT
A.dept_no,
A.emp_no,
B.salary
FROM
dept_emp A
INNER JOIN salaries B ON A.emp_no = B.emp_no
) C
) D
WHERE D.raking = 1
ORDER BY D.dept_no;
根据部门和工资进行排序,取每个部门的排名第一。
在进行数据提取。
法2:
select r.dept_no,ss.emp_no,r.maxSalary from (
select d.dept_no,max(s.salary)as maxSalary from dept_emp d,salaries s
where d.emp_no=s.emp_no
group by d.dept_no
)as r,salaries ss,dept_emp dd
where r.maxSalary=ss.salary
and r.dept_no=dd.dept_no
and dd.emp_no=ss.emp_no;