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

个人网站

首发于公众号小肖学数据分析

描述

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

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

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

解题思路：

1、获取每个部门最高薪水的员工

2、对部门的薪水进行排序，利用rank() over() ，对薪水降序排列，取第一条

3、部门编号dept_no升序排列

-- 创建测试数据

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 dept_emp VALUES(10004,'d002','1996-08-03','9999-01-01');

INSERT INTO dept_emp VALUES(10005,'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');

INSERT INTO salaries VALUES(10004,92527,'2001-08-02','9999-01-01');

INSERT INTO salaries VALUES(10005,52342,'2001-08-02','9999-01-01');

查询脚本

SELECT

dept_no,

emp_no,

salary

FROM

(

SELECT

b.dept_no,

b.emp_no,

a.salary,

rank() over ( PARTITION BY b.dept_no ORDER BY a.salary DESC ) rt

FROM

salaries a

INNER JOIN dept_emp b ON a.emp_no = b.emp_no

) a

WHERE

rt = 1

order by dept_no

公众号回复加油即可获取PDF版本