SQL Interview Prep · 课时

各部门最高薪资者

Q: 「各部门最高薪资者」课时是免费的吗？

是的 — 「各部门最高薪资者」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 SQL Interview Prep 课程共包含 4 节课。

Q: 「各部门最高薪资者」这节课中我会学到什么？

结合分区与排名，解决分组取前 N 名薪资的问题 你通过在浏览器中直接运行的动手代码来练习 SQL Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

Q: 学习 SQL Interview Prep 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。

Q: 「各部门最高薪资者」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

Q: 我能在这节 SQL Interview Prep 课中编写并运行代码吗？

能。每节 SQL Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

结合分区与排名，解决分组取前 N 名薪资的问题

各部门最高薪资者 是 CoddyKit 上的免费 SQL Interview Prep 课时。这是第 3 节课，共 4 节。你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。这是 SQL Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Interview Prep 课程共包含 4 节课。

从全局排名到按组排名

下一个进阶问题是：“找出每个部门薪资最高的员工。”这将排名与分组结合起来，是一道很典型的中级面试题。

假设有一个 employee 表，其中包含 id、name、department_id 和 salary。我们的目标是找出每个部门一名（并列时可能多名）最高薪资员工，而不只是全局最大值。

这里新增的关键工具是 PARTITION BY，它会在每个部门内部重新开始排名。

CREATE TABLE employee (
  id            INT PRIMARY KEY,
  name          VARCHAR(100),
  department_id INT,
  salary        INT
);

PARTITION BY 会重置排名

在窗口定义中加入 PARTITION BY department_id，就会告诉数据库在每个部门内独立计算排名。

每个部门都会从自己的排名 1 开始。因此，部门 1 的最高薪资员工和部门 5 的最高薪资员工都会获得排名 1。如果不进行分区，只有唯一的全局最大值会获得排名 1。

SELECT name, department_id, salary,
       DENSE_RANK() OVER (
         PARTITION BY department_id
         ORDER BY salary DESC
       ) AS rnk
FROM employee;

过滤出排名 1

要只保留最高薪资员工，请将排名查询放入子查询，然后过滤排名 1。和往常一样，必须先在子查询或 CTE 中计算窗口函数，之后才能对它进行过滤。

在这里使用 DENSE_RANK（或 RANK）意味着，如果某个部门中有两名员工并列最高薪资，都会被返回。这通常是对“最高薪资员工”的正确理解。

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk = 1;

需要恰好一个时使用 ROW_NUMBER

有时，面试官希望每个部门恰好返回一行，即使存在平局。此时请使用 ROW_NUMBER，并添加确定性的平局决胜条件，例如选择最小的标识符。

没有平局决胜条件时，平局会以任意方式解决，结果也不是确定性的。添加 , id ASC 后，选择结果便可重复。

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, id ASC
         ) AS rn
  FROM employee
) t
WHERE rn = 1;

此处的 DENSE_RANK、ROW_NUMBER 与 RANK 对比

请根据题目的确切措辞进行选择：

DENSE_RANK = 1: 每个部门中所有并列最高薪资的员工。
RANK = 1: 对于最高排名，与 DENSE_RANK 完全相同（只有排名 1 以下才会出现间隔）。
ROW_NUMBER = 1: 每个部门恰好一名员工，平局由您的 ORDER BY 打破。

说明您选择了哪一个以及原因，是面试官评分的重点。

窗口函数出现之前的相关子查询方法

在窗口函数出现之前，标准解决方案是相关子查询：仅保留同一部门中没有其他人薪资更高的行。

这种方法自然会返回所有并列的最高收入者。它具有良好的可移植性，但可能运行缓慢，因为除非优化器对其进行改写，否则内部的 MAX 会针对每个外部行计算一次。

SELECT e.name, e.department_id, e.salary
FROM employee e
WHERE e.salary = (
  SELECT MAX(e2.salary)
  FROM employee e2
  WHERE e2.department_id = e.department_id
);

GROUP BY 连接方法

另一种可移植的模式是：使用 GROUP BY 计算每个部门的最高薪资，然后连接回去以获取匹配的员工。

这种方法高效且清晰。连接会返回所有薪资等于所在部门最高薪资的员工，因此会保留平局。

SELECT e.name, e.department_id, e.salary
FROM employee e
JOIN (
  SELECT department_id, MAX(salary) AS max_sal
  FROM employee
  GROUP BY department_id
) m
  ON e.department_id = m.department_id
 AND e.salary = m.max_sal;

每个部门的前 N 名

这个模式可以扩展为“每个部门收入最高的 3 人”，不需要新的思路。只需将过滤条件改为一个范围。

使用 DENSE_RANK 时，rnk <= 3 会返回前三个不同的薪资档位（出现平局时，行数可能多于三行）。使用 ROW_NUMBER 时，rn <= 3 会为每个部门恰好返回三行。

SELECT name, department_id, salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
WHERE rnk <= 3;

示例

部门 1：Ana 120、Bob 120、Cara 90。部门 2：Dan 200、Eve 150。

DENSE_RANK = 1: 部门 1 的 Ana（120）和 Bob（120）；部门 2 的 Dan（200）。共三行。
带标识符平局决胜条件的 ROW_NUMBER = 1: Ana 和 Bob 中的一人（取标识符较小者），加上 Dan。共两行。

相同的数据，根据所使用的函数不同，行数也会不同。请选择与题意相符的函数。

包含部门并连接名称

面试官经常会添加一个 department 表，并要求返回部门名称。只需在排名完成后连接该表。

请将排名保留在 employee 表上，并在最后连接查找表，这样分区仍会在正确的粒度上进行。

SELECT d.name AS department, t.name AS employee, t.salary
FROM (
  SELECT name, department_id, salary,
         DENSE_RANK() OVER (
           PARTITION BY department_id ORDER BY salary DESC
         ) AS rnk
  FROM employee
) t
JOIN department d ON d.id = t.department_id
WHERE t.rnk = 1;

应避免的常见问题

按组排名时常见的错误包括：

忘记 PARTITION BY 而进行全局排名，结果只返回整个公司的最高收入者。
当题目暗示所有并列者都应出现时使用 ROW_NUMBER，导致并列最高收入者被悄悄丢弃。
试图直接将窗口函数放入 WHERE，而不是将其包裹起来。
在排名前连接部门表，意外改变分区粒度。

快速检查

请根据要求选择正确的排名函数。

回顾

每个部门的最高收入者问题，就是全局排名模式加上 PARTITION BY department_id：

DENSE_RANK = 1 返回每个部门所有并列的最高收入者。
带有平局决胜条件的 ROW_NUMBER = 1 会为每个部门恰好返回一人。
可移植的替代方案：按部门使用相关 MAX，或使用 GROUP BY 计算最高值后再连接回原表。

将 = 1 改为 <= N 即可扩展为前 N 名。请明确说明您如何处理平局。

常见问题解答