SQL数据排名后如何筛选前三？窗口函数配合子查询实战技巧

拿到一组销售数据或者成绩数据之后，一个常见的需求冒了出来：先按某个维度排名，然后取出每个分组的前三名。比如每个部门绩效前三的员工、每个品类销量前三的商品、每个班级分数前三的学生。很多初学者第一反应是写ORDER BY加LIMIT，结果发现取出来的是全局前三而不是每组前三，这就是典型的排名后筛选问题。要优雅地解决它，需要先理解SQL的执行顺序，再掌握窗口函数配合子查询的经典套路。

一、为什么排名后筛选必须用子查询或者CTE

理解这个问题的关键在于SQL语句的逻辑执行顺序。一条完整的查询并不是按照书写顺序执行的，实际的执行顺序大致是FROM、WHERE、GROUP BY、HAVING、SELECT、ORDER BY，而窗口函数是在SELECT阶段才计算的。这就带来一个矛盾：ROW_NUMBER()这类窗口函数产生的列在WHERE阶段根本还不存在，所以如果你直接在外层写WHERE rn <= 3去引用排名列，数据库会直接报错，提示列不存在。

解决思路也很直接：把排名的计算放到内层查询中，让内层查询先执行完毕并生成名次列，外层查询再把这个结果当成一张临时表来过滤。写法上有两种选择，一种是嵌套子查询，一种是公用表表达式（CTE）。两者在大多数数据库中性能没有本质区别，但CTE的可读性明显更好，特别是在排名逻辑复杂、需要多次引用中间结果的时候。

-- 写法一：CTE方式（推荐，可读性好）
WITH ranked AS (
    SELECT
        dept,
        emp_name,
        salary,
        ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employee
)
SELECT dept, emp_name, salary
FROM ranked
WHERE rn <= 3;

-- 写法二：嵌套子查询方式
SELECT *
FROM (
    SELECT
        dept,
        emp_name,
        salary,
        ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
    FROM employee
) t
WHERE t.rn <= 3;

注意外层过滤条件写rn <= 3而不是rn = 3，前者是取前三名，后者只取第三名，这一点在实际开发中踩坑的人不少。

二、ROW_NUMBER、RANK与DENSE_RANK该选哪一个

三种排名函数的行为差异主要体现在遇到相同值时的处理上。ROW_NUMBER不管值是否重复，一律给出连续且唯一的编号，1、2、3、4排下去，并列的记录会按照某种顺序被强行区分开。RANK对并列值给相同名次，但会跳过后面的名次，比如两个人并列第一，下一个直接是第三名。DENSE_RANK同样给并列值相同名次，但不会跳号，并列第一之后下一个是第二名。

这三种函数对应三种不同的业务语义。如果需求是严格的前三个人，不管成绩是否相同，用ROW_NUMBER；如果需求是成绩排在前三档的所有人，并列算同一档，用DENSE_RANK；如果需求是标准竞赛规则下的前三名（允许并列第一导致实际人数少于三人），用RANK。选错函数会导致结果与业务预期不符，比如用ROW_NUMBER取成绩前三，两个并列第一的学生可能只有一个被选上，业务方往往会质疑数据正确性。

另外要留意ORDER BY中排序字段如果有NULL值，不同数据库对NULL的默认排序位置不一样，MySQL默认NULL最小排最后，Oracle默认NULL最大排前面。稳妥的做法是在排序表达式中显式加上NULLS LAST或者用COALESCE处理，避免跨库迁移时结果悄悄变化。

三、各数据库中的差异写法与性能优化建议

主流数据库中，MySQL从8.0版本开始支持窗口函数，8.0之前的版本只能用变量模拟或者自连接的方式实现排名，写法繁琐且容易出错，条件允许的话建议升级。PostgreSQL、Oracle、SQL Server对窗口函数的支持都比较完整。此外SQL Server的老版本里有一个非标准的TOP配合子查询的写法，也能实现类似效果。

-- MySQL 8.0之前的替代方案：用户变量模拟排名
SELECT dept, emp_name, salary
FROM (
    SELECT
        dept, emp_name, salary,
        @rn := IF(@grp = dept, @rn + 1, 1) AS rn,
        @grp := dept
    FROM employee, (SELECT @rn := 0, @grp := NULL) vars
    ORDER BY dept, salary DESC
) t
WHERE rn <= 3;

性能方面，窗口函数的排序开销是主要成本。当数据量较大时，确保PARTITION BY和ORDER BY涉及的列上有合适的复合索引可以避免额外的排序操作。比如上面员工表的例子，建立一个(dept, salary)的复合索引，多数数据库就能利用索引顺序完成分区排序，执行计划中不再出现昂贵的sort算子。

还有一个容易被忽略的点：内层查询尽量只选必要的列，不要图省事写SELECT *。窗口函数计算需要物化中间结果，列越多占用的内存和临时表空间越大。如果分组数量特别多而每个分组只有几条记录，也可以考虑在应用层做过滤，或者在表设计阶段就维护好排名字段，通过定时任务更新，查询时直接读取，用空间换时间的思路应对高并发场景。

总结一下，排名后筛选前三的完整套路就是三步：用PARTITION BY定义分组，用ORDER BY定义排名规则，把排名结果包进子查询或CTE后在外层用WHERE过滤名次。掌握这个模式之后，每组前N名、每组最新一条记录、去重保留一条这类问题都可以套用同样的骨架去解决，可以说是SQL进阶过程中最有性价比的一个技巧。

窗口函数子查询SQL排名修改时间：2026-09-17 10:00:23