MySQL练习 —— 牛客网SQL第十二题解法（求取每个分组的最大值）

先给出题目的建表语句和插入数据的语句

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,92525,'2001-08-02','9999-01-01');

select * from dept_emp;
select * from salaries;

dept_emp 表格内容

salaries 表格内容

题目要求是：获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号dept_no升序排列

我的解答是：

select dd.dept_no,dd.emp_no,ss.maxSalary
from (select d1.emp_no,d1.dept_no,salary from dept_emp as d1,salaries as s1 where d1.emp_no=s1.emp_no) as dd,
(select dept_no,max(salary) as maxSalary from dept_emp as d2,salaries as s2
where d2.emp_no = s2.emp_no
group by dept_no) as ss
where dd.dept_no = ss.dept_no and dd.salary=ss.maxSalary
order by dd.dept_no;

运行结果为

具体的思路可以分为几个过程
首先因为使用group by语句后，select语句中的字段只能是group by后面的字段，以及聚集函数里面的字段。这就导致了不可能直接将两张表连接，然后分组查询最大的薪水，如果这么干，最后输出的结果是没有emp_no的值的

所以这里先将dept_emp 和 salaries表连接起来，提取里面的emp_no，dept_no，和salary字段

select d1.emp_no,d1.dept_no,salary from dept_emp as d1,salaries as s1 where d1.emp_no=s1.emp_no

然后再将dept_emp 和 salaries表连接起来，这次提取的字段是dept_no 和 max(salary)
提取 max(salary)字段是为了获得每个部门的最大薪资
提取 dept_no字段是为了与上面的那张表进行连接

select dept_no,max(salary) as maxSalary from dept_emp as d2,salaries as s2
where d2.emp_no = s2.emp_no
group by dept_no

将上面求得的两张表连接起来

select dd.dept_no,dd.emp_no,ss.maxSalary
from (select d1.emp_no,d1.dept_no,salary from dept_emp as d1,salaries as s1 where d1.emp_no=s1.emp_no) as dd,
(select dept_no,max(salary) as maxSalary from dept_emp as d2,salaries as s2
where d2.emp_no = s2.emp_no
group by dept_no) as ss
where dd.dept_no = ss.dept_no and dd.salary=ss.maxSalary
order by dd.dept_no;

两张表连接在一起后，dd.salary=ss.maxSalary 如果某个员工的薪资与最高薪资一样，那么就输出这个员工的信息