如何使用MySQL查询每个城市薪资第二高的员工信息

涉及表结构 #

• 员工详情表 Employeedetails：字段包含 empid（员工唯一ID）、fullname（员工全名）、city（员工所属城市）
• 员工薪资表 emloyeesalary：字段包含 empid（关联员工详情表的员工ID）、salary（员工薪资）

方案1：MySQL 8.0及以上版本（窗口函数实现，性能更优） #

使用 DENSE_RANK() 窗口函数处理同薪资并列场景，避免同城市多个最高薪资时无法匹配到第二高的问题：

WITH ranked_emp AS (
    SELECT 
        ed.empid,
        ed.fullname,
        ed.city,
        es.salary,
        DENSE_RANK() OVER (PARTITION BY ed.city ORDER BY es.salary DESC) AS salary_rank
    FROM Employeedetails ed
    INNER JOIN emloyeesalary es ON ed.empid = es.empid
)
SELECT empid, fullname, city, salary
FROM ranked_emp
WHERE salary_rank = 2;

说明：如果需要跳过并列排名（即并列第一名有2人时，第二高对应总排名第3的薪资），可将DENSE_RANK()替换为RANK()。

方案2：MySQL 5.x及更低版本（无窗口函数兼容实现） #

通过子查询先匹配每个城市的第二高薪资，再关联获取员工信息：

SELECT 
    ed.empid,
    ed.fullname,
    ed.city,
    es.salary
FROM Employeedetails ed
INNER JOIN emloyeesalary es ON ed.empid = es.empid
WHERE (ed.city, es.salary) = (
    SELECT city, MAX(salary) AS second_salary
    FROM (
        SELECT ed1.city, es1.salary
        FROM Employeedetails ed1
        INNER JOIN emloyeesalary es1 ON ed1.empid = es1.empid
        WHERE es1.salary < (
            SELECT MAX(es2.salary)
            FROM Employeedetails ed2
            INNER JOIN emloyeesalary es2 ON ed2.empid = es2.empid
            WHERE ed2.city = ed1.city
        )
    ) AS t
    WHERE t.city = ed.city
);

注意事项 #

• 请确认薪资表名emloyeesalary的拼写和实际业务表一致，若为拼写笔误可替换为正确表名
• 若某城市有效薪资记录不足2条，查询结果默认不会返回该城市数据
• 两种方案均默认返回同城市所有薪资为第二高的员工（含并列第二的场景）

内容的提问来源于stack exchange，提问作者Nikola Prgin