牛客网 SQL12 获取每个部门中当前员工薪水最高的相关信息

题目

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');
#获取每个部门中当前员工薪水最高的相关信息

写题思路

1、汇总两个表

2、用where a.rank = 1; 把每个区的第一拉出来形成结果表

代码

select b.*
from(
select a.dept_no, a.emp_no,a.from_date, a.to_date, s.salary,
dense_rank() over(partition by a.dept_no order by s.salary desc ) salary_rank
from dept_emp a join salaries s
on a.emp_no = s.emp_no) b
where b.salary_rank = 1;