MySQL查询各部门第二高薪资：连接+子查询实现方案探讨

给定两张表emp(id,ename,sal,deptid)和dept(deptid,dname)，可以通过子查询结合连接实现查询每个部门的dname、ename及第二高薪资的需求。

原代码的问题 #

• 用NOT IN (SELECT MAX(sal) FROM emp GROUP BY deptid)排除最高薪的逻辑有缺陷：若部门存在多名员工拿最高薪，或部门仅1名员工时，结果会失真
• 分组仅按e.deptid，但SELECT子句包含d.name和empID，不符合SQL分组规范（多数数据库会触发语法错误）
• 无法精准关联到拿第二高薪的员工姓名ename

正确实现代码 #

SELECT 
    d.dname,
    e.ename,
    s.second_max_sal
FROM 
    dept d
LEFT JOIN (
    -- 子查询：获取各部门第二高薪资
    SELECT 
        deptid,
        MAX(sal) AS second_max_sal
    FROM 
        emp e1
    WHERE 
        sal < (SELECT MAX(sal) FROM emp e2 WHERE e2.deptid = e1.deptid)
    GROUP BY 
        deptid
) s ON d.deptid = s.deptid
LEFT JOIN emp e ON s.deptid = e.deptid AND s.second_max_sal = e.sal;

逻辑说明 #

1. 内层子查询：针对每个部门，先筛选出薪资低于该部门最高薪的所有记录，再取这些记录的最大值，得到部门的第二高薪资
2. 与dept表连接：关联部门ID，获取对应的部门名称dname
3. 与emp表连接：通过部门ID和第二高薪资，匹配出对应的员工姓名ename
4. 使用LEFT JOIN可保留无第二高薪的部门（如仅1名员工的部门），若只需返回有第二高薪的部门，替换为INNER JOIN即可

内容的提问来源于stack exchange，提问作者Jaydeep Chouhan