SQL 进阶2每个部门薪资排名前 2 的员工查询窗口函数与 DENSE_RANK1. 一道高频SQL面试题背后的真实业务场景1.1 为什么“部门Top N”会成为基本功分水岭先说个我面试别人时的真实感受。很多候选人写得出复杂到爆的多表 JOIN聚合函数用得飞起但一碰到“分组后取每组前几名”这类需求立刻卡壳。这道题之所以经典是因为它恰好卡在 SQL 能力的中间地带——你从“能把数据查出来”迈向“能高效地解决业务问题”中间必须翻过这道坎。真实业务里这类需求的出镜率高得吓人。人力资源要发季度奖每个部门绩效最好的两个人拿什么档位运营要看转化每个渠道带来订单最多的两个销售是谁财务要做成本分析每个成本中心开销最大和次大的科目明细。本质都是同一个句式按某个维度分组再在组内按某个指标排序最后筛出前 N 条。在 SQL Server、MySQL 8.0、PostgreSQL、Oracle 这些主流数据库里现在的标准答案是窗口函数加排名函数。但如果你去翻 2010 年以前的教材或者碰一个还在用 MySQL 5.7 的老系统情况就完全不一样了——这也是为什么理解不用窗口函数的老写法同样有价值。你先看懂了“旧世界的解法”才能真正理解窗口函数到底解决了什么痛点。1.2 不依赖窗口函数的旧解法自连接与子查询的局限假设有一张员工表字段分别是姓名、部门和薪资。在窗口函数普及之前要查出“每个部门薪资排名前 2 的员工”常用的思路是自连接对每个员工统计同部门里薪资严格高于他的人数如果这个人数小于 2说明他排在部门前两位。SELECT e1.dept, e1.name, e1.salary FROM emp e1 WHERE ( SELECT COUNT(DISTINCT e2.salary) FROM emp e2 WHERE e2.dept e1.dept AND e2.salary e1.salary ) 2 ORDER BY e1.dept, e1.salary DESC;逻辑上没问题同部门比当前人薪资高的人只有 0 个或 1 个那当前人不是第一就是第二。但你先感受一下这个写法有哪几个不舒服的地方——第一子查询对每一行都要执行一次表一大人就麻了第二语句的意图被嵌套子查询淹没读起来要反应半天第三如果哪天需求改成“前 3 名”你得回头改那个数字但改的时候得仔细想清楚逻辑有没有漏洞。当时还有一种写法是用用户变量做模拟排序在 MySQL 5.7 及更早版本里很流行大致思路是先按部门和薪资排序然后用一个变量记录“同一部门内的行号”。可变量作用域和赋值顺序稍微搞错一点结果就莫名其妙地不对调试体验极差。也因此当窗口函数正式进入各大数据库的标准语法后这类需求的第一反应应当直接转向新写法。它不是为了让你少写几行代码而是让“分组排序”这个高频语义第一次有了干净、规范、可读性强的表达方式。2. 三个排名函数一字之差RANK、DENSE_RANK、ROW_NUMBER 的底层差异2.1 窗口函数语法的执行顺序聊 DENSE_RANK 之前先把窗口函数这块垫脚石铺平。窗口函数的标准语法长这样SELECT dept, name, salary, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_num FROM emp;拆开看就三部分函数本身、OVER子句、以及OVER里面的分组排序定义。PARTITION BY dept是“把整个表按部门切成一摞一摞的小牌堆”每个牌堆独立计算互不干扰。ORDER BY salary DESC是“在每个牌堆内部按薪资从高到低排队”。DENSE_RANK()是“给排好队的每一个人发一个名次”。合起来读这句 SQL 就是在告诉数据库把员工按部门分开每个部门内按薪资降序排列然后给每个人按 DENSE_RANK 规则发名次。这里有个关键点窗口函数是在SELECT 阶段执行的。SQL 的逻辑执行顺序大致是 FROM → WHERE → GROUP BY → HAVING → SELECT窗口函数在这一步 → ORDER BY → LIMIT。这意味着你不能在 WHERE 里直接过滤一个别名rank_num因为 WHERE 执行的时候窗口函数还没算出来。这也是无数新手写这个查询时踩的第一个坑后面我会专门展开。2.2 并列薪资场景下的三种行为对比窗口函数家族里有三个长得几乎一模一样的排名函数ROW_NUMBER()、RANK()、DENSE_RANK()。你要是只记住“它们都能排名”就去写 SQL后面一定出问题。我建了个最简单的场景某部门三个人薪资分别是 10000、10000、8000。三个人用三种函数分别排名结果完全不一样员工薪资ROW_NUMBERRANKDENSE_RANK张三10000111李四10000211王五8000332ROW_NUMBER不管有没有并列强制按物理顺序编号所以张三 1、李四 2、王五 3。RANK遇到并列时并列的人拿相同名次但下一个名次会跳过——两个人的薪资都是 1那第三个人直接排到 3名次序列出现了跳号。DENSE_RANK的“DENSE”是“稠密”的意思它同样给并列者相同名次但下一个名次不跳号——8000 没有被甩到 3而是紧跟在后面拿 2。这个差异在“排名前 2 的员工”这个需求里要命。如果用 ROW_NUMBER薪资相同的两个人会被硬拆成第 1 和第 2但业务上他们明明应该并列第一。如果用 RANK且部门里有并列第一那这个部门可能出现“第 1、第 1、第 3”前 2 名里反而只选出两个人但如果按取前 2 个名次来看可能是三人——第 1 名并列的两个人都应被包含第 3 名被排除。而 DENSE_RANK 的语义和“按名次区间取人”的需求严丝合缝只要名次落在 1 到 2 区间内就该被选出来并列的人一个都不少。2.3 为什么这个需求选择 DENSE_RANK回到标题里的核心问题——每个部门薪资排名前 2 的员工。如果领导嘴上说的是“前 2 名”那业务层面通常有两层意思第一层严格取两行不管有没有并列就要两行数据比如抽两个人去参加培训名额有限不能多去。这种情况应该用 ROW_NUMBER。第二层取排名前 2 的员工薪资最高的那一档、第二高的那一档的人都要。如果第一名有两个人并列那这两个人都是第一都得拿出来。这种情况必须用 DENSE_RANK。大多数“每个部门薪资排名前 2”的面试题和报表需求语义上更偏向第二层——按名次区间选取。这就是为什么标题里点名要用 DENSE_RANK。我先把这个结论摆在这儿下文会给你看完整的结果对比到时候你就明白这三个函数在不同并列组合下输出差异有多明显。从面试角度来说能脱口说出三者在并列场景下的差异比背出窗口函数的语法更容易让面试官记住你。这直接说明你是真的理解语义而不是只会复制粘贴。3. 完整落地建表、造数、写查询与结果验证3.1 建表与测试数据准备纸上谈兵没意思直接把整个库建起来跑一遍。我用 MySQL 8.0 的语法写这个写法在 PostgreSQL、SQL Server、Oracle 上基本通用顶多改改自增列和字符串类型定义。CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(50) NOT NULL, dept VARCHAR(50) NOT NULL, salary DECIMAL(10,2) NOT NULL ); INSERT INTO emp (name, dept, salary) VALUES -- 技术部两个并列第一一个第三 (张三, 技术部, 10000), (李四, 技术部, 10000), (王五, 技术部, 8000), -- 销售部明显的梯队 (赵六, 销售部, 12000), (钱七, 销售部, 11000), (孙八, 销售部, 9000), (周九, 销售部, 8500), -- 市场部只有一个人边界情况 (吴十, 市场部, 7000);这份测试数据我故意埋了几个雷技术部有两个薪资一样的人并列第一销售部正常四档薪资市场部只有一个人。三张情况合在一起正好能把前面说的三种函数差异、并列问题、部门人数不足问题一次性全暴露出来。3.2 核心查询的逐步拆解为什么非要套一层子查询完整 SQL 长这样SELECT dept, name, salary, rank_num FROM ( SELECT dept, name, salary, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_num FROM emp ) t WHERE rank_num 2 ORDER BY dept, rank_num;这里有一个新手十有八九会犯的错直接在 WHERE 里写rank_num 2然后发现报错或查询失败。原因我前面提到过窗口函数在 SELECT 阶段才计算WHERE 执行时这个值根本不存在。所以必须用子查询把算好排名结果当成一张临时表再在外部用 WHERE 过滤。你可以把内层子查询想象成一个“预加工车间”原料是原始员工表车间里给每个人都贴上一个“部门内名次”的标签然后整个交付出去。外层查询的任务就简单了只管按标签筛人。这种“先计算再过滤”的结构是窗口函数查询的标准模板记下来后面所有 Top N 需求都能套。3.3 验证结果与分析并列怎么处理、缺人怎么处理用上面的测试数据跑完结果如下deptnamesalaryrank_num市场部吴十70001销售部赵六120001销售部钱七110002技术部张三100001技术部李四100001技术部王五80002这里有几个细节值得盯着看技术部因为并列第一最终输出了 3 条数据而不是 2 条。张三和李四都是第一名王五作为第二名也被带出来了。如果用 ROW_NUMBER结果会变成只有张三和李四王五被挤出去但张三和李四谁是第一名其实是随机的这就跟“按名次选人”的业务意图矛盾了。用 RANK王五的排名会变成 3直接掉出前两名。你看三者差异在一组真实数据面前一眼就能看穿。市场部只有一个人照样输出一行排名第 1不会报错也不会漏。这个边界情况很多人造数时想不到但线上表里数据分布复杂一个部门确实可能只有一个员工这个查询天然能兜住这种情况不用担心。ORDER BY dept, rank_num 这行是保证输出的可读性先按部门排序再按名次排。SQL 里子查询的结果如果不加外部排序顺序是不保证的所以外部这层 ORDER BY 一定不能漏。4. 边界情况与编码陷阱这些坑我替你先踩过了4.1 并列第一会不会多出人到底该用哪个排序函数上一节的输出已经展示得很直白了用 DENSE_RANK 会在存在并列第一的情况下多输出一个人。这个“多”到底是不是 bug取决于业务口径。我举一个具体例子帮你理解。假设公司发“季度优秀员工”奖项规定每个部门薪资前 2 的人有资格参评并列薪资的人都是同一档那么技术部的张三、李四、王五三个人都应该出现在名单里。这时候 DENSE_RANK 是对的多出来的王五不是多而是业务上他本来就够格。但如果业务方的需求是“每个部门挑两个人来参加团建名额不能超”那应该用 ROW_NUMBER强行编号哪怕并列也得给你拆出先后。所以这个问题在写 SQL 之前就必须跟业务方确认清楚。我的经验是第一版默认用 DENSE_RANK因为“按排名区间选人”是更常见的业务语义如果对方明确说“就要 N 行”再切成 ROW_NUMBER。4.2 为什么不能直接在 WHERE 里写 rank_num这个坑出现频率太高了我单独拎出来说一下。很多人写完内层查询就顺手在外层加条件-- 错误示例 SELECT dept, name, salary, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_num FROM emp WHERE rank_num 2;数据库会直接报错Unknown column rank_num in where clause。原因很本质SQL 里 SELECT 后面的别名在 WHERE 阶段是不可见的。WHERE是逐行过滤的而DENSE_RANK()是要等所有行都排完序才能算出来的东西两者根本不在一个时间维度上。要过滤排名只能通过子查询或 CTE 把排名结果物化成一个中间结果再取。如果你用的是 SQL Server、PostgreSQL 或者 MySQL 8.0 以上版本还可以写 CTE 让可读性更好一点WITH ranked AS ( SELECT dept, name, salary, DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_num FROM emp ) SELECT dept, name, salary, rank_num FROM ranked WHERE rank_num 2 ORDER BY dept, rank_num;这俩写法等价的我个人偏爱 CTE 版本因为它把“算排名”和“筛排名”分得更清楚而且一直下推到后面多个步骤时不用嵌套那么深。4.3 部门人数不足、NULL薪资与字符集问题再列几个实际项目中容易翻车的边界条件。部门人数不足 2 人。市场部只有一个人查询依然能正常返回一行不会因为找不到第二名就报错。但如果业务方要求“不足 2 人的部门也要显示出来不足的部分补 NULL”DENSE_RANK 直接查是做不到的需要配合VALUES或递归 CTE 生成一个部门×序号的笛卡尔参照表再 LEFT JOIN。这种需求在报表里很常见面试里问到的少但实际工作里遇到了别慌思路是“补序列”而不是“改排名”。薪资为 NULL。ORDER BY salary DESC时NULL 值在不同数据库里的排序位置不同。MySQL 里 NULL 排在最后Oracle 里 NULL 排最前。如果你的表里薪资允许为空一定要提前确认业务上空薪资的员工算不算排名。如果算要先用COALESCE(salary, 0)之类的函数把 NULL 转成默认值再排否则你根本说不清为什么某个员工掉出了前两名。字符集与排序规则。PARTITION BY dept按部门分组时如果部门字段在不同表里用了不同字符集会导致 JOIN 或分组结果出乎意料。比如一张表是 utf8mb4_general_ci另一张是 utf8mb4_unicode_ci分组时会把“技术部”和“技术部”识别为不同的组。这个坑跟窗口函数本身无关但我在实际项目里真的见过有人排查了半小时都找不到为什么分组结果多了一倍最后发现是字符集不统一。5. 从Top 2到Top N窗口函数的一鱼多吃5.1 同款需求变体每个分类最近一条、每组最高值、环比对比“每个部门薪资前 2”学会之后你的武器库里等于多了一把瑞士军刀。我把同系列的高频变体列一下每一个都是真实需求改个函数和排序字段的事。每个部门薪资最高的员工把rank_num 2改成rank_num 1或者干脆用ROW_NUMBER() 1更省事。每门课程成绩前 3 名的学生表换成score(student, course, score)分区字段换成course排序改成ORDER BY score DESC过滤条件 3。跟工资场景一模一样。每个客户最近一笔订单这是电商和 CRM 系统里的高频需求。WITH ordered AS ( SELECT customer_id, order_id, order_time, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_time DESC) AS rn FROM orders ) SELECT * FROM ordered WHERE rn 1;注意这里用的是 ROW_NUMBER因为“最近一笔”是物理上唯一的不存在并列问题用 ROW_NUMBER 能保证每行都有确定编号。选择哪个排名函数不是看心情而是看业务语义里“并列”是否有意义。每个省份销售额环比用 LAG 或 LEAD 拿上一期数据。SELECT dept, month, amount, amount - LAG(amount) OVER (PARTITION BY dept ORDER BY month) AS month_over_month_change FROM sales;LAG 是窗口函数家族的另一个成员它让你能访问同一分组内上一行的值。说实话LAG 和 LEAD 这两兄弟在报表里的价值一点都不比排名函数低只是一般人入门窗口函数都是从排名函数开始的。5.2 性能优化要点什么时候慢、怎么让它快窗口函数也不是银弹数据量上来之后性能问题就是绕不开的话题。我之前帮人优化过一个集团的报表就是“每个片区营收前 10 的门店”两张千万级表 JOIN 之后做窗口函数跑了 20 多秒还超时。先说性能瓶颈在哪窗口函数执行时数据库需要按PARTITION BY分组再按ORDER BY排序。如果没有合适的索引这一步就是全表扫描加 filesort数据量一大必然慢。优化手段按优先级排第一尽量缩窄开窗前的数据集。把那些明显不需要的分区、时间范围先通过 WHERE 过滤掉。窗口函数是在过滤之后的结果上执行的你提前挡住 90% 的数据它就能快 10 倍。第二联合索引要建对。对(dept, salary)建联合索引能让 PARTITION BY dept 和 ORDER BY salary 都走索引大幅减少 filesort。注意顺序分区字段在前排序字段在后。第三别把窗口函数塞进太复杂的视图里。有些同事喜欢把所有逻辑都堆在一个视图里结果视图被 JOIN 了七八次每次查询都把窗口函数重算一遍。这种情况把算好排名结果的中间表物化会快得多。我见过最极端的一个案例优化前 13 秒加联合索引 提前过滤后降到 0.8 秒。所以别一听窗口函数就说它慢慢的不是函数本身是没让它在合适的数据范围里跑。5.3 面试延伸问题这题还能怎么问这道题在面试里经常被当做一个引子后面跟着一连串追问我把常见的几个列出来你们提前预习。“如果不用窗口函数怎么实现”——就是我第一段写的自连接或者用户变量模拟排序。面试官考的是你知道 SQL 的发展脉络以及没有窗口函数时你的兜底能力。“ROW_NUMBER、RANK、DENSE_RANK 有什么区别”——用我上文的并列薪资例子回答再补一句“如果业务要求名额固定 N 个用 ROW_NUMBER要求按名次区间取人用 DENSE_RANK”。“窗口函数的执行顺序是怎样的”——答出它在 WHERE 和 GROUP BY 之后执行所以不能在 WHERE 里过滤别名要包子查询或 CTE。“数据量很大怎么优化”——提前过滤 联合索引 避免在复杂视图里重复计算这三点足够应对绝大多数场景。我把这道题看成一个基本功体检项目。窗口函数和 DENSE_RANK 只是入口它们背后是 SQL 的逻辑执行顺序、分组排序语义、索引设计思路和业务沟通能力。把这些都想明白了以后再遇到“每个 XX 排名前 N 的 YY”这类需求你就不会再去网上搜答案了直接手写顺手还能给旁边的同事讲明白为什么用 DENSE_RANK 而不是 ROW_NUMBER——这种一眼看穿需求本质的能力才是你真正从增删改查迈向数据分析的开始。