AI秒写窗口函数，3秒查出各部门最高工资

2026-06-10阅读 0热度 0

窗口函数解题：为什么它是面试官的首选

SQL面试中，“分组取Top N”是高频考点，其中“每个部门工资最高的员工”出现的频率仅次于两数之和。很多人能写出答案，但往往用子查询嵌套自连接——性能差、代码丑陋，还容易漏掉并列情况。

这道题的标准解法其实是窗口函数（ROW_NUMBER / RANK / DENSE_RANK）。可惜不少候选人一紧张就忘了语法：OVER里怎么配 PARTITION BY 和 ORDER BY？RANK 和 ROW_NUMBER 有何区别？

实际上，没必要死记硬背，AI一秒就能生成。但关键在于——你必须能讲清楚为什么选这个函数，以及窗口函数的执行顺序。今天就用这道真实面试题，演示如何借助AI秒解SQL难题，同时把窗口函数彻底讲透。

核心观点：面试官考察的不是你能否背出语法，而是你是否理解“为什么”以及“选哪个”。

一、面试题原貌：部门工资最高查询

表结构

CREATE TABLE employee (
    id INT,
    name VARCHAR(50),
    department VARCHAR(50),
    salary INT
);

需求：查询每个部门工资最高的员工。如果最高工资有并列（比如两人都是10000），都要返回。

示例数据

id	name	department	salary
1	张三	技术部	12000
2	李四	技术部	10000
3	王五	技术部	12000
4	赵六	销售部	9000

期望输出：技术部返回张三和王五（并列12000），销售部返回赵六。

二、AI生成的标准答案

在AI工具里输入：

用MySQL语法，写一个SQL查询：每个部门工资最高的员工。如果最高工资有多人，全部返回。要求使用窗口函数，并解释执行顺序。

AI输出：

WITH ranked AS (
    SELECT 
        *,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk
    FROM employee
)
SELECT id, name, department, salary
FROM ranked
WHERE rnk = 1;

为什么用 RANK 而不是 ROW_NUMBER？

ROW_NUMBER 会给每人一个唯一序号，并列的也会强行分出先后，导致只能返回一人。
RANK 会为相同工资分配相同排名，并列第一就都返回。

面试官接着追问：那 DENSE_RANK 呢？答：DENSE_RANK 排名连续，但本题只需要最高工资，效果与 RANK 一样（因为只取 rnk=1）。但如果有第二高需求，DENSE_RANK 和 RANK 的区别就大了。

三、窗口函数的执行顺序（讲清楚才能加分）

面试官接着问：“窗口函数是在SQL哪个阶段执行的？”

回答：在WHERE、GROUP BY之后，在ORDER BY之前。具体顺序：

FROM → JOIN
WHERE → 过滤行
GROUP BY → 分组
聚合函数计算
HA VING → 过滤分组
窗口函数 → 在这里计算排名
ORDER BY
LIMIT

所以，窗口函数的结果可以在WHERE里用吗？不能，因为WHERE在窗口函数之前执行。所以必须用CTE或子查询先计算排名，再在外部WHERE筛选。

AI生成的代码正是这样做的：WITH ranked AS ( ... ) 先算排名，再 WHERE rnk = 1。听到这里，面试官点了头。

四、如果不用窗口函数，你能写出更优的解法吗？

可以用子查询+关联，但性能差且代码臃肿：

SELECT e1.*
FROM employee e1
LEFT JOIN employee e2 
    ON e1.department = e2.department 
    AND e1.salary < e2.salary
WHERE e2.id IS NULL;

这个解法的逻辑是：找出不存在同部门更高工资的人。优点是跨数据库通用，缺点是不好理解，而且对于大表性能差（因为自连接扫描两次）。

用窗口函数，不仅快（一次扫描），而且清晰易维护。所以现代SQL强烈推荐。

核心观点：不用窗口函数的SQL优化，就像不用箭头函数的JS——能跑，但不优雅。

五、面试官为什么认可你用AI？

同样的逻辑：AI帮你生成语法，你负责解释原理。他知道你背不出完整的RANK语法（正常人谁背？），但你知道什么时候用RANK、窗口函数执行顺序、如何改造成其他需求。这就够了。

六、拓展：分组取第二名怎么改？

面试官可能接着问：“如果我要每个部门第二高工资呢？”

很简单，把 WHERE rnk = 1 改成 WHERE rnk = 2 就行。但注意：如果第二名是并列（比如两人都是9000），RANK会跳过第三名的序号（比如排名：1,2,2,4），DENSE_RANK 则连续（1,2,2,3）。你需要问清楚需求。

七、完整代码和测试数据

你可以用这个数据自测：

CREATE TABLE employee (
    id INT PRIMARY KEY,
    name VARCHAR(20),
    department VARCHAR(20),
    salary INT
);
INSERT INTO employee VALUES
(1,'张三','技术部',12000),
(2,'李四','技术部',10000),
(3,'王五','技术部',12000),
(4,'赵六','销售部',9000);

然后运行上面的窗口函数SQL，输出应该是：张三、王五、赵六。

八、写在最后

面试题在变，但考察的核心没变：理解原理 > 背诵语法。AI能帮你写出任何代码，但能讲清楚为什么、怎么改、有什么坑，才是你的真本事。