MySQL经典习题与解法Num01

题目：获取每个部门中当前员工薪水最高的相关信息

描述：

有一个员工表dept_emp简况如下:

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d001	1996-08-03	9999-01-01
10003	d002	1996-08-03	9999-01-01

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	92527	2001-08-02	9999-01-01

示例表数据：

drop table if exists  `dept_emp` ; 
drop table if exists  `salaries` ; 
CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO dept_emp VALUES(10001,'d001','1986-06-26','9999-01-01');
INSERT INTO dept_emp VALUES(10002,'d001','1996-08-03','9999-01-01');
INSERT INTO dept_emp VALUES(10003,'d002','1996-08-03','9999-01-01');

INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,92527,'2001-08-02','9999-01-01');

问：获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列，以上例子输出如下:

dept_no	emp_no	maxSalary
d001	10001	88958
d002	10003	92527

方案1：采用MAX聚合函数求出最高薪资

思路：1、先求出每个部门的最高薪资salary，作为临时表

           2、JOIN连接dept_emp表和salaries表，同用筛选语句where在临时表中找出各部门中薪资salary最大的。

select d.dept_no,d.emp_no,s.salary
from dept_emp as d
inner  join salaries as s
on  d.emp_no = s.emp_no
where s.salary in (
    select max(s2.salary)
    from dept_emp as d2
    inner join salaries as s2
    on  d2.emp_no = s2.emp_no
    and d2.dept_no = d.dept_no
)
order by d.dept_no,d.emp_no;

方案二：采用RANK窗口函数分部门按薪资倒序排序，选择排在第一位的记录

SELECT t.dept_no,t.emp_no,t.salary AS maxSalary 
    FROM (SELECT d.dept_no,d.emp_no,s.salary,
            DENSE_RANK() OVER (PARTITION BY d.dept_no ORDER BY s.salary DESC) AS sal_rank
            FROM dept_emp d 
            INNER JOIN salaries s 
            ON d.emp_no = s.emp_no) t
    WHERE t.sal_rank = 1
    ORDER BY t.dept_no;

方案三：ROW_NUMBER( )序号函数-顺序排序

select t.dept_no, t.emp_no, t.salary as maxSalary
from (
  select a.salary, a.emp_no, b.dept_no, 
  row_number() over(partition by b.dept_no order by a.salary desc) as rn
  from salaries a
  left join dept_emp b on a.emp_no = b.emp_no
) t
where t.rn = 1

注意：

1. SELECT 中的每一个字段都必须在 GROUP BY 中，或者它必须被一个聚合函数所包围。
2. DENSE_RANK()序号函数-并列排序，为结果集中的每一行分配一个排名值，相同值的行具有相同的排名，没有间隔。
3. 使用GROUP BY子句时，SELECT子句中只能有聚合键、聚合函数、常数