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

牛客网链接

描述

有一个员工表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

 

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

dept_no	emp_no	maxSalary
d001	10001	88958
d002	10003	92527

示例1

输入：

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');

输出：

d001|10001|88958
d002|10003|92527

题解：

　　1）两张表关联，按部门分组，查询出每个部门的最大薪水，这张关联后的表作为一张临时表a

　　2）三张表关联，根据部门、员工和最大薪水

　　3）最后根据dept_emp表的dept_no字段排序

补充：

　　1）inner join 返回两个表中都有的数据

　　2）left join 会从左表中返回所有的值，即使右表中没有匹配

　　3）right join 会从右表中返回所有的值，即使左表中没有匹配

select
    d.dept_no,
    s.emp_no,
    s.salary
from
    dept_emp d
    join salaries s
    join (
        select
            d.dept_no dept_no,
            max(s.salary) salary
        from
            dept_emp d
            join salaries s on d.emp_no = s.emp_no
        group by
            d.dept_no
    ) a on d.emp_no = s.emp_no
    and d.dept_no = a.dept_no
    and s.salary = a.salary
order by
    d.dept_no;