SQL JOIN查询各部门薪资前三名员工的高效方法

最直接的解法，就是甩出窗口函数 ROW_NUMBER() 配合 PARTITION BY。在 JOIN 后面接一个子查询或者 CTE，对每个部门按薪水降序排个号，再筛出 rn = 1 的记录。别想着用 GROUP BY + MAX()，那玩意儿只能把最高薪数值揪出来，对应员工是谁、叫什么名儿，一概不知，属于典型的“拿错了药方”。

用窗口函数 ROW_NUMBER() 配合 PARTITION BY 是最直接解法

直接在 JOIN 后加子查询或 CTE，对每个部门按薪水降序编号，再筛选 rn。别用 GROUP BY + MAX()，那只能查出最高薪数值，查不到对应员工信息。

常见错误是写成：SELECT dept, MAX(salary) FROM emp GROUP BY dept —— 这根本拿不到“前三名员工”的姓名、ID 等字段，属于典型语义误用。

实操建议：

• 必须用 ROW_NUMBER()（不是 RANK() 或 DENSE_RANK()），除非你明确需要并列时跳号（比如两个 20000 并列第一，下一个就是第三名）
• PARTITION BY department_id 要和 JOIN 的部门字段严格一致，注意别漏掉表别名，比如写成 PARTITION BY d.id 却忘了 d 是部门表别名
• 排序用 ORDER BY salary DESC, employee_id ASC，避免薪水相同时结果不稳定（尤其分页或多次执行）

JOIN 顺序和过滤时机决定性能关键

先关联再排序编号，比先筛再 JOIN 更安全。如果先用子查询把每个部门前 3 名算出来，再 JOIN 部门表，能避免全表扫描；反过来，如果先 JOIN 再 ROW_NUMBER()，数据量大时内存压力明显。

示例结构（以 PostgreSQL/MySQL 8.0+ 为例）：

WITH ranked AS (
  SELECT
    e.employee_id,
    e.name,
    e.salary,
    e.department_id,
    ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC, e.employee_id ASC) AS rn
  FROM employees e
)
SELECT r.*, d.name AS dept_name
FROM ranked r
JOIN departments d ON r.department_id = d.id
WHERE r.rn <= 3;

注意：WHERE r.rn <= 3 必须放在最外层，不能写在 CTE 里——否则优化器可能无法下推过滤条件，导致计算全部行的序号。

MySQL 5.7 或 SQLite 等不支持窗口函数？改用相关子查询

这类数据库没法用 ROW_NUMBER()，但硬要实现“每个部门前三”，就得靠 (SELECT COUNT(*) ...) 统计同部门更高薪人数。性能差，只适合小表（<1000 行）。

典型写法：

SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE (
  SELECT COUNT(*)
  FROM employees e2
  WHERE e2.department_id = e1.department_id
    AND e2.salary > e1.salary
) < 3;

陷阱：

• 没有 ORDER BY 时，相同薪水员工可能被随机截断，实际返回不固定三人
• 索引必须包含 (department_id, salary)，否则子查询会全表扫描，慢到不可接受
• 如果某部门只有两人，这条语句仍正确返回两人；但若用 LIMIT 3 就完全不对——LIMIT 是全局限制，不是每组限制

别忽略 NULL 和重复薪水带来的语义偏差

薪水字段为 NULL 时，ORDER BY salary DESC 默认把 NULL 排最后（标准 SQL 行为），但某些旧版 MySQL 可能排最前，导致 ROW_NUMBER() 编号错乱。显式写成 ORDER BY salary DESC NULLS LAST 更稳妥（PostgreSQL 支持，MySQL 不支持，需用 IFNULL(salary, 0) 替代）。

重复薪水场景下：ROW_NUMBER() 强制唯一编号，RANK() 会产生并列（如：20000、20000、19000 → 1、1、3），DENSE_RANK() 是 1、1、2。选哪个取决于业务定义——“前三名”是否允许并列。

最容易被忽略的是：部门表里存在但员工表无记录的空部门（比如刚建的部门还没招人），此时 LEFT JOIN 会返回 NULL 员工行。如果需求是“只查有员工的部门”，就用 INNER JOIN；如果必须列出所有部门（含空部门），就得额外处理 rn 字段的 NULL 情况。