如何编写SQL查询各部门最高薪资并展示所有员工的完整信息

需求描述 #

查询各部门最高薪资，同时展示所有员工的对应信息。

表结构及测试数据 #

CREATE TABLE EMP 
(
    `EID` INTEGER,
    `ENAME` VARCHAR(30),
    `DEPT` VARCHAR(30),
    `SALARY` INTEGER
);

INSERT INTO EMP (`EID`, `ENAME`, `DEPT`,`SALARY`)
VALUES (111, 'BABAUL', 'HR', 3000),
       (112, 'SILK', 'IT', 1000),
       (113, 'LEATHER', 'PAN', 4000),
       (221, 'COTTON', 'IT', 2000),
       (222, 'SILK', 'PAN', 5000),
       (223, 'LEATHER', 'HR', 6000),
       (224, 'LEATHER', 'ELEC', 1500);

样例输入数据 #

EID ENAME   DEPT    SALARY
--------------------------
111 BABAUL  HR      3000
112 SILK    IT      1000
113 LEATHER PAN     4000
221 COTTON  IT      2000
222 SILK    PAN     5000
223 LEATHER HR      6000
224 LEATHER ELEC    1500

预期输出结果 #

EID ENAME   DEPT    SALARY  MAX_salary
---------------------------------------
224 LEATHER ELEC    1500     1500
111 BABAUL  HR      3000     6000
223 LEATHER HR      6000     6000
112 SILK    IT      1000     2000
221 COTTON  IT      2000     2000
113 LEATHER PAN     4000     5000
222 SILK    PAN     5000     5000

实现SQL #

方案1：窗口函数实现（兼容MySQL8.0+、PostgreSQL、Oracle等主流数据库） #

用开窗函数按部门分组取最高薪资，无需关联子查询，写法简洁性能更高：

SELECT 
    EID,
    ENAME,
    DEPT,
    SALARY,
    MAX(SALARY) OVER (PARTITION BY DEPT) AS MAX_salary
FROM EMP
ORDER BY DEPT, EID;

方案2：关联子查询实现（兼容MySQL5.x等不支持窗口函数的低版本数据库） #

先聚合得到每个部门的最高薪资，再和原员工表关联匹配：

SELECT 
    e.EID,
    e.ENAME,
    e.DEPT,
    e.SALARY,
    m.MAX_salary
FROM EMP e
INNER JOIN (
    SELECT DEPT, MAX(SALARY) AS MAX_salary
    FROM EMP
    GROUP BY DEPT
) m ON e.DEPT = m.DEPT
ORDER BY e.DEPT, e.EID;

内容的提问来源于stack exchange，提问作者Mrutyunjay Nayak