1. 从一次成绩排名的需求说起做数据库开发的朋友应该都有过这种经历业务方拿着一份成绩单过来说“我要按成绩排名”你第一反应是先ORDER BY score DESC把数据捞出来然后排个序号等数据量上来、或者业务加了“按班级排名”、“按科目排名”、“只取前三名”这类条件之后你会发现普通的ORDER BY完全不够用。我在实际项目里处理过不少成绩相关报表最后沉淀下来的核心工具就是partition by配合窗口函数。这篇文章是 MS SQL Server 里partition by实战的第三篇主题锁定在“成绩排名”这个经典场景。前面我们聊过分组聚合和行列转换这一篇专门针对排名需求从最基础的row_number()到并列排名如何处理再到复杂的分组内排名把日常报表开发里最常见的几种写法一次讲透。无论你是做教务系统、考试平台还是企业内部考核报表这套思路都是通用的。先说个前提这篇文章里的示例都是在 SQL Server 2019 上实测过的用到的表结构和数据我都放在文末方便你直接复制去跑。版本低的也没关系窗口函数从 SQL Server 2005 开始就支持了2008 R2 以上的版本基本都能跑通。2. 重新理解 partition by它不是分组是“开窗”2.1 普通分组和窗口分区的区别一次说清楚很多刚接触partition by的人会把它理解成GROUP BY的另一种写法这是最大的误区。我打个比方GROUP BY是把一群人按班级分成几个房间每个房间只能出来一个“代表”这个代表可能是平均分、总人数或者最高分而partition by是给每个人发一张带房间号的标签大家还站在原地但你可以让每个人都知道自己在自己房间里的排名。换成 SQL 术语来说GROUP BY会压缩行数每一组只保留一行聚合结果而PARTITION BY不会压缩行数它只是在每一行上附加一个计算出来的值这个值的计算范围限制在对应的分区内。这个特性正是做排名报表时最关键的一点——你需要保留每一名学生的明细记录同时附上这名学生在某个范围内的名次。举个例子下面这条语句SELECT 班级, 学号, 姓名, 成绩, ROW_NUMBER() OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) AS 班级内排名 FROM 成绩表;执行结果里每一行都还在学号、姓名这些明细一个都没少只是多了一列“班级内排名”。如果是GROUP BY 班级你永远只能得到每个班级一行数据根本拿不到学生级别的明细。这个差异决定了窗口函数在报表场景里是不可替代的。2.2 窗口函数执行的时机理解了就不容易写错要真正用好partition by得知道它在 SQL 执行流程里的位置。SQL Server 处理一条查询时大致顺序是FROM → WHERE → GROUP BY → HAVING → 窗口函数 → SELECT → ORDER BY。窗口函数是在GROUP BY、HAVING之后执行的这也意味着窗口函数可以使用WHERE过滤后的结果集但不能在窗口函数里引用SELECT子句中新取的别名。我在实际项目中犯过这样的错-- 错误写法 SELECT 学号, 成绩, ROUND(成绩, 0) AS 折算分, ROW_NUMBER() OVER(ORDER BY 折算分 DESC) AS 排名 FROM 成绩表;这段代码会直接报错因为折算分这个别名是在SELECT阶段才生成的而窗口函数执行时机在SELECT之前。正确做法是先算好折算分再在外面包一层查询。SELECT 学号, 折算分, ROW_NUMBER() OVER(ORDER BY 折算分 DESC) AS 排名 FROM ( SELECT 学号, ROUND(成绩, 0) AS 折算分 FROM 成绩表 ) AS t;这种细节网上很少讲透但写错了就是硬错误排查起来还挺容易让人蒙圈的。2.3 排名字段选什么int 和 bigint 的取舍在创建存储过程或临时表存储排名结果时排名列的数据类型我建议直接用INT。别觉得这是小事我见过有人在表设计里把排名列设成VARCHAR(10)结果排序时“10”排在“9”前面闹出过笑话。排名结果一般不会超过 21 亿INT完全足够而且和ROW_NUMBER()返回的类型一致后续做比较运算不用转型省掉一顿麻烦。3. 成绩排名的三大主力函数row_number、rank、dense_rank3.1 三个函数的语法与返回结果对比SQL Server 里做排名有三个现成的窗口函数ROW_NUMBER()、RANK()和DENSE_RANK()。它们长得像但行为差异非常关键尤其是遇到成绩相同的情况时。我用一组实际数据来演示。假设有 5 名学生成绩分别是 95、90、90、85、80学号成绩ROW_NUMBER()RANK()DENSE_RANK()A00195111A00290222A00390322A00485443A00580554从表里能清楚看到ROW_NUMBER()不管成绩是否相同强制给每一行分配一个不重复的序号序号连续不间断。RANK()成绩相同则名次相同但下一个名次会跳号。90 分两人并列第二下一名直接跳到第 4 名。DENSE_RANK()成绩相同则名次相同而且名次不跳号。90 分两人并列第二下一名是第 3 名。选择哪个函数取决于业务规则。如果是体育比赛的淘汰赛分组名次跳号不影响晋级名额用RANK()没问题如果只是普通成绩单展示希望名次连续用DENSE_RANK()更友好如果系统里每个学生的排名必须唯一比如排座位、生成唯一编号那就只能用ROW_NUMBER()。3.2 给排名函数加并行排序条件消除不确定性刚才那张表里ROW_NUMBER() 给 90 分的那两行随机分配了第 2 和第 3 名。实际上 SQL Server 会按照物理存储顺序分配看起来像“随机”。正式报表里这种不确定性是隐患比如两次跑出来的名次不一样业务方会质疑数据有问题。解决办法是加并列排序条件。比如成绩相同时按学号升序作为次级排序SELECT 学号, 姓名, 成绩, ROW_NUMBER() OVER(ORDER BY 成绩 DESC, 学号 ASC) AS 稳定排名 FROM 成绩表;这样成绩相同的学生学号在前的排在前面谁先谁后就是确定的了。这个操作我在做考试成绩归档时几乎每次都会加就是怕后续对账时发现排名对不上。3.3 并列排名在业务里的实际处理方式如果是考试发奖状“并列第二”是能说出口的直接用RANK()或DENSE_RANK()就行了。但如果这个排名要作为“按名次发放奖学金档位”的依据就得小心了。比如一等奖要求第 1 名二等奖 2-3 名三等奖 4-6 名用RANK()出来 5 个人并列第 2那一等奖之外的人到底算几等光靠 SQL 返回的名次没法直接判断。我常用的做法是先算出DENSE_RANK()作为并列名次展示再用ROW_NUMBER()生成一个内部使用的唯一序号用于奖励等级判断。一张表里同时保留两列一列给业务看一列给系统逻辑用各司其职谁都不得罪。4. 经典实战场景拆解排行榜、班级排名与前 N 名4.1 全校总分排行榜附带参与排名人数先来看最基础的需求全校所有学生按总分从高到低排一个总榜。这里要注意数据粒度的转换——每个学生可能有多科成绩得先按学生汇总总分再开窗排名。WITH StudentTotal AS ( SELECT 学号, SUM(成绩) AS 总分 FROM 成绩表 GROUP BY 学号 ) SELECT 学号, 总分, ROW_NUMBER() OVER(ORDER BY 总分 DESC) AS 名次, COUNT(*) OVER() AS 参与排名人数 FROM StudentTotal;这里用了两个窗口函数ROW_NUMBER()生成名次COUNT(*) OVER()返回整个结果集的行数。COUNT(*) OVER()不加PARTITION BY时作用于整个结果集效果等同于在每一行上附加一个总行数。这样报表页面上可以很直接地展示“本次考试共 328 人参与排名”这类信息不需要额外执行一条COUNT(*)查询。这个技巧在做报表看板的时候特别实用能省一次往返数据库的查询。需要注意的是这里的总计依赖GROUP BY先完成汇总窗口函数在分组结果上接着算顺序是对的。如果你想在窗口函数里直接对明细成绩做SUM()得到的是每个科目累加的结果跟总分排名不是一回事别混淆。4.2 班级内部排名看清同一张卷子在不同班的分布学校经常要做“班级排名”目的是看同一个老师教的不同班级之间同一次考试的成绩差异。这种场景下PARTITION BY 班级就派上大用场了。SELECT 班级, 学号, 姓名, 成绩, RANK() OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) AS 班级排名 FROM 成绩表 WHERE 科目 数学;这条语句的执行逻辑是先用WHERE把数学成绩过滤出来然后 SQL Server 按班级把数据划分成若干分区在每个分区内独立计算排名。理科班的第一名是 1文科班的第一名也是 1互不干扰。如果还要同时展示年级排名可以在同一句话里再加一列SELECT 班级, 学号, 姓名, 成绩, RANK() OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) AS 班级排名, RANK() OVER(ORDER BY 成绩 DESC) AS 年级排名 FROM 成绩表 WHERE 科目 数学;一条 SQL 同时输出班级排名和年级排名这就是窗口函数叠加的威力。报表上两个排名一对比哪些学生在班级里靠前但年级里靠后、哪些学生在班里不起眼但在年级是黑马一眼就看出来了。这种需求在线下教学质量分析里非常常见用partition by写起来干净利落。4.3 按班级科目分组排名处理多维度排行榜再进一步业务要的是“每个班每一科的单科排名”。这就是多列PARTITION BY的问题。SELECT 班级, 科目, 学号, 姓名, 成绩, ROW_NUMBER() OVER( PARTITION BY 班级, 科目 ORDER BY 成绩 DESC ) AS 单科班级排名 FROM 成绩表;PARTITION BY后面可以跟多个列它们共同定义一个分区。这里(班级, 科目)作为一个整体分区键每个班级的每门科目都被视为独立的排名空间。SQL Server 会按这两个列的组合值对数据进行逻辑分组然后在每个组内独立排序和编号。执行计划里能看到一个专门的“Segment”运算符它负责识别分区边界。当分区键发生变化时SQL Server 会重置内部计数器重新从 1 开始编号。理解了这个机制你就明白了为什么窗口函数比自连接、子查询的方案性能更好——它只需要对数据做一次排序然后顺序扫描即可完成所有分区的排名计算。如果需求是“每个班级每科前 3 名”可以在上面基础上再套一层SELECT * FROM ( SELECT 班级, 科目, 学号, 姓名, 成绩, ROW_NUMBER() OVER( PARTITION BY 班级, 科目 ORDER BY 成绩 DESC ) AS 排名 FROM 成绩表 ) AS t WHERE 排名 3;注意窗口函数不能直接放在WHERE子句里因为窗口计算发生在WHERE过滤之后。必须用子查询包一层在外层再过滤排名 3。这个模式是“分组 Top N”问题的标准解法写多了你就熟了。4.4 平均分之上的“相对位置”如何找偏科生纯排名的价值有限业务方真正想看的往往是排名背后反映的问题。比如年级排名靠前但某科班级排名垫底的学生就是典型的“偏科生”。这类查询也是partition by的拿手好戏。WITH StudentStats AS ( SELECT 学号, 姓名, AVG(成绩) OVER(PARTITION BY 学号) AS 个人均分, 成绩, ROW_NUMBER() OVER( PARTITION BY 学号 ORDER BY 成绩 ASC ) AS 最弱科目序号 FROM 成绩表 ) SELECT 学号, 姓名, 个人均分, 成绩 AS 最弱科目成绩 FROM StudentStats WHERE 最弱科目序号 1;这里的关键思路是以学号为分区键对每个学生的多科成绩从小到大排列取出序号为 1 的那一科就是该学生的“最弱科目”。同时用AVG() OVER(PARTITION BY 学号)算出个人平均分一对比就能看出这个学生的短板科目比个人均分低了多少。这种写法避免了多次自连接一次扫描完成所有学生的个人画像在几百个学生、几千条成绩记录的场景下性能表现很不错。5. 高级实战移动平均、累计排名和第 N 名查找5.1 用窗口函数实现累计排名与分段统计排名除了“绝对名次”还有一种“相对名次”的玩法。比如想知道成绩排在前 30% 的学生有哪些直接用RANK()再除以总人数就行。WITH Ranked AS ( SELECT 学号, 成绩, RANK() OVER(ORDER BY 成绩 DESC) AS 名次, COUNT(*) OVER() AS 总人数 FROM 成绩表 ) SELECT 学号, 成绩, 名次, 总人数, CAST(名次 AS FLOAT) / 总人数 * 100 AS 百分比排名 FROM Ranked WHERE 名次 总人数 * 0.3;百分比排名在教育行业用得很多比如“这个学生超过了全校 85% 的同学”。SQL Server 也有专门的PERCENT_RANK()和CUME_DIST()窗口函数不过我觉得用RANK() / COUNT(*)这种基础写法更直观也更容易向业务解释。计算出百分比排名之后分段统计就顺理成章了——前 10% 是 A 档、10%-30% 是 B 档、30%-60% 是 C 档这种规则只需要再套一个CASE WHEN就能实现。5.2 查询每个班级的第 2 名OFFSET 窗口的妙用“找出每个班级第二名”这类需求传统的子查询写法非常啰嗦要处理并列情况更是头疼。用窗口函数 OFFSET就简单太多了。SELECT 班级, 学号, 成绩 FROM ( SELECT 班级, 学号, 成绩, ROW_NUMBER() OVER( PARTITION BY 班级 ORDER BY 成绩 DESC ) AS 班级排名 FROM 成绩表 ) AS t WHERE 班级排名 2;如果想取“每个班级第 2 到第 4 名”就改成WHERE 班级排名 BETWEEN 2 AND 4。想取“去掉第一名之后的前三人”先不考虑并列的话就是WHERE 班级排名 BETWEEN 2 AND 4跟上面一样。如果有并列成绩需要按业务规则决定是取RANK()还是ROW_NUMBER()前面讲过区分方法这里不重复。如果用的是 SQL Server 2012 以上的版本还有更优雅的写法——OFFSET 1 ROW FETCH NEXT 3 ROWS ONLY它可以在每个分区内跳过第一名再往下取 3 行。不过实际用下来多数报表场景直接用ROW_NUMBER() 外层WHERE过滤就足够清晰了OFFSET语法对不常用窗口函数的人来说反而费解。5.3 滚动排名近 3 次考试的平均名次成绩分析里还有一个常见需求看一个学生最近几次考试的综合表现趋势。用partition by配合ROWS BETWEEN窗口框架可以算移动平均值。WITH ExamOrder AS ( SELECT 学号, 考试时间, 成绩, ROW_NUMBER() OVER( PARTITION BY 学号 ORDER BY 考试时间 DESC ) AS 考试序号 FROM 成绩表 ) SELECT 学号, 考试时间, 成绩, AVG(成绩) OVER( PARTITION BY 学号 ORDER BY 考试序号 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS 近三次平均分 FROM ExamOrder;ROWS BETWEEN 2 PRECEDING AND CURRENT ROW的意思是窗口范围从当前行的前两行开始到当前行结束共 3 行的跨度。配合ORDER BY 考试序号实现的就是“最近 3 次考试的平均分”。这种滑动窗口的分析能力是传统 SQL 写法很难优雅实现的如果不用窗口函数你需要多层嵌套子查询加ROW_NUMBER()自连接代码量翻好几倍性能还更差。6. 性能调优与常见坑我亲手踩过的6.1 为什么你的窗口函数查询慢先看执行计划窗口函数的性能瓶颈主要在排序操作上。ORDER BY子句要求 SQL Server 对分区内的数据排序如果表数据量大且没有合适的索引这一步会产生大量的内存授予和临时库写入查询自然就慢了。有一回我处理一张 500 万行成绩表的班级排名查询第一次跑用了 20 多秒。打开执行计划看发现 Sort 运算符占了 80% 的成本。解决办法是给(班级, 成绩 DESC)建了一个复合索引再跑一次只需要 3 秒。因为PARTITION BY 班级和ORDER BY 成绩 DESC正好对应复合索引的前两列SQL Server 可以从索引里直接按顺序读取数据连排序都省了。索引设计上没有万能公式但有一个通用参考分区列放在索引前面排序列放在后面方向尽量和ORDER BY一致如果PARTITION BY是班级, 科目索引应该建(班级, 科目, 成绩 DESC)如果只是单纯的ORDER BY 成绩 DESC全局排名建(成绩 DESC)索引通常就够了建议每次写完窗口函数查询都打开“显示估计的执行计划”看看有没有 Sort 运算符、有没有 Table Scan这是性能排查的第一步。6.2 统计信息过时导致的排名错乱这是一个比较隐蔽的问题。如果表数据量变化剧烈但统计信息没更新查询优化器可能选择错误的执行策略返回的结果顺序也可能和预期不符。我遇到过一次某次月考后导入了新数据跑成绩排名时发现有两个学生的名次对调了排查了很久最后发现是统计信息的采样率太低导致的。解决方法是定期更新统计信息尤其是在批量导入数据后UPDATE STATISTICS 成绩表;更稳妥的做法是写一个维护计划每天凌晨对主要业务表执行UPDATE STATISTICS。这个操作开销不大但对查询性能的稳定性帮助非常大。成绩这类数据经常有大批量导入的场景导入后如果不更新统计信息SQL Server 依然按旧的数据分布来估算行数可能导致内存授予不足、排序落到 tempdb性能骤降。6.3 排序字符集和 NULL 值带来的名次差异成绩排序时如果字段类型是VARCHAR排序规则按字符字典序排。比如“9”会排在“82”后面因为字符 9 大于 8。这种问题在从 Excel 导入数据时特别容易遇到——明明存的是数字导入时被转成了文本。所以成绩表的成绩字段我强烈建议用DECIMAL(5,1)或DECIMAL(5,2)既支持小数又能保证排序正确。还有一个容易被忽略的坑是NULL值。SQL Server 默认排序里NULL被视为最小值ORDER BY 成绩 DESC时NULL会排在最后但ORDER BY 成绩 ASC时NULL会排在最前。如果你不希望NULL参与排名用WHERE 成绩 IS NOT NULL过滤掉如果必须保留又想强制NULL排在末尾可以加一个辅助排序列ORDER BY CASE WHEN 成绩 IS NULL THEN 1 ELSE 0 END, 成绩 DESC;这个CASE列让有成绩的永远排在前面没成绩的沉底。实测中这个写法在导出报表时非常常用避免了 NULL 排名混进名次序列里的尴尬。6.4 老版本 SQL Server 的兼容性注意事项虽然窗口函数从 2005 年就有了但有些更高级的写法是老版本不支持的。比如ROWS BETWEEN窗口框架是 2012 年才加入的OFFSET FETCH也是。如果你还在维护 SQL Server 2008 R2 的系统用ROWS BETWEEN会直接报语法错误。老版本的替代方案是自连接SELECT a.学号, AVG(b.成绩) AS 近三次平均分 FROM 成绩表 a JOIN 成绩表 b ON a.学号 b.学号 AND b.考试序号 BETWEEN a.考试序号 - 2 AND a.考试序号 GROUP BY a.学号;逻辑没错但性能差不少尤其数据量大时要反复扫描。所以有条件的话还是建议尽早升级到 2016 以上版本新版本不仅窗口函数更完善还有STRING_AGG等字符串聚合函数写报表轻松很多。7. 常用排名查询速查手册这里把我日常开发中最常用到的几类排名查询整理成一个速查表方便你直接抄作业需求场景推荐写法关键点全校排名无并列ROW_NUMBER() OVER(ORDER BY 成绩 DESC)名次唯一全校排名并列不跳号DENSE_RANK() OVER(ORDER BY 成绩 DESC)名次连续全校排名并列跳号RANK() OVER(ORDER BY 成绩 DESC)名次可能中断班级内排名RANK() OVER(PARTITION BY 班级 ORDER BY 成绩 DESC)分区独立排名班级科目排名ROW_NUMBER() OVER(PARTITION BY 班级, 科目 ORDER BY 成绩 DESC)多列分区每个班前3名子查询 WHERE 排名 3不能直接 WHERE 窗口函数每个班第2名子查询 WHERE 排名 2调整数字即可找最弱科目ROW_NUMBER() OVER(PARTITION BY 学号 ORDER BY 成绩 ASC)升序取出最小近3次平均分AVG() OVER(... ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)需要日期排序这张表覆盖了我在成绩类报表中遇到的最常见需求。实际业务可能还有一些变体比如“排除缺考后排名”、“只排及格学生”本质上都是在窗口计算之前先用WHERE做好数据筛选然后再套上面这些写法逻辑是一样的。8. 两个容易被忽略的细节技巧第一个是窗口函数不要和DISTINCT混用。如果对窗口结果做DISTINCTSQL Server 会先计算窗口函数再去除重复行这不仅完全没必要还可能造成语义偏差。比如你SELECT DISTINCT 学号, ROW_NUMBER() OVER(ORDER BY 成绩 DESC)因为每一行的窗口值不同DISTINCT根本不会去重任何行白白浪费资源。遇到这种情况先想想是不是应该用GROUP BY而不是用DISTINCT硬凑。第二个是在存储过程里做排名时尽量用表变量或临时表把排名结果物化。原因很简单如果你在一个存储过程里多次引用窗口函数的计算结果SQL Server 会重复执行相同的窗口计算。虽然执行计划可能会缓存但多一层临时表可以让数据流更清晰也方便后续调试。我习惯把排名结果先SELECT ... INTO #temp存进临时表后面所有过滤、排序、展示都基于临时表操作代码可读性大幅提升排查问题也简单。关于临时表还要提醒一句如果临时表的数据量超过 5000 行记得在临时表上建索引。否则后面关联其他表时临时表会被反复扫描性能会很难看。我在一个日常跑批的存储过程里因为一张 2 万行的临时表没建索引导致整个批处理从 10 分钟变成 40 分钟加上索引后秒回。这个坑可以说教训深刻。9. 写在最后的一点个人体会做了这些年数据库开发我越来越觉得窗口函数是 SQL 能力的分水岭。会写partition by的人和只会GROUP BY的人面对同样一个“成绩排名”需求写出来的代码量可能是 20 行和 80 行的区别可读性和性能差距更是天壤之别。partition by的价值不在于它多复杂而在于它给了你一种“在明细行上做分析”的思维方式让你不用再费劲地用自连接、子查询去模拟本来很简单的逻辑。这篇实战里所有的示例都是我实际上线过或验证过的写法你可以直接拿过去跑。成绩排名只是partition by的一个切入点理解了分区、排序、窗口范围这三个核心概念之后你会发现它能做的事情远比“排名”本身要广得多——移动平均、同比环比、占比重计算全是同一套思维。希望这篇能帮你少走一些我当年走过的弯路。
SQL Server partition by实战：成绩排名场景详解
1. 从一次成绩排名的需求说起做数据库开发的朋友应该都有过这种经历业务方拿着一份成绩单过来说“我要按成绩排名”你第一反应是先ORDER BY score DESC把数据捞出来然后排个序号等数据量上来、或者业务加了“按班级排名”、“按科目排名”、“只取前三名”这类条件之后你会发现普通的ORDER BY完全不够用。我在实际项目里处理过不少成绩相关报表最后沉淀下来的核心工具就是partition by配合窗口函数。这篇文章是 MS SQL Server 里partition by实战的第三篇主题锁定在“成绩排名”这个经典场景。前面我们聊过分组聚合和行列转换这一篇专门针对排名需求从最基础的row_number()到并列排名如何处理再到复杂的分组内排名把日常报表开发里最常见的几种写法一次讲透。无论你是做教务系统、考试平台还是企业内部考核报表这套思路都是通用的。先说个前提这篇文章里的示例都是在 SQL Server 2019 上实测过的用到的表结构和数据我都放在文末方便你直接复制去跑。版本低的也没关系窗口函数从 SQL Server 2005 开始就支持了2008 R2 以上的版本基本都能跑通。2. 重新理解 partition by它不是分组是“开窗”2.1 普通分组和窗口分区的区别一次说清楚很多刚接触partition by的人会把它理解成GROUP BY的另一种写法这是最大的误区。我打个比方GROUP BY是把一群人按班级分成几个房间每个房间只能出来一个“代表”这个代表可能是平均分、总人数或者最高分而partition by是给每个人发一张带房间号的标签大家还站在原地但你可以让每个人都知道自己在自己房间里的排名。换成 SQL 术语来说GROUP BY会压缩行数每一组只保留一行聚合结果而PARTITION BY不会压缩行数它只是在每一行上附加一个计算出来的值这个值的计算范围限制在对应的分区内。这个特性正是做排名报表时最关键的一点——你需要保留每一名学生的明细记录同时附上这名学生在某个范围内的名次。举个例子下面这条语句SELECT 班级, 学号, 姓名, 成绩, ROW_NUMBER() OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) AS 班级内排名 FROM 成绩表;执行结果里每一行都还在学号、姓名这些明细一个都没少只是多了一列“班级内排名”。如果是GROUP BY 班级你永远只能得到每个班级一行数据根本拿不到学生级别的明细。这个差异决定了窗口函数在报表场景里是不可替代的。2.2 窗口函数执行的时机理解了就不容易写错要真正用好partition by得知道它在 SQL 执行流程里的位置。SQL Server 处理一条查询时大致顺序是FROM → WHERE → GROUP BY → HAVING → 窗口函数 → SELECT → ORDER BY。窗口函数是在GROUP BY、HAVING之后执行的这也意味着窗口函数可以使用WHERE过滤后的结果集但不能在窗口函数里引用SELECT子句中新取的别名。我在实际项目中犯过这样的错-- 错误写法 SELECT 学号, 成绩, ROUND(成绩, 0) AS 折算分, ROW_NUMBER() OVER(ORDER BY 折算分 DESC) AS 排名 FROM 成绩表;这段代码会直接报错因为折算分这个别名是在SELECT阶段才生成的而窗口函数执行时机在SELECT之前。正确做法是先算好折算分再在外面包一层查询。SELECT 学号, 折算分, ROW_NUMBER() OVER(ORDER BY 折算分 DESC) AS 排名 FROM ( SELECT 学号, ROUND(成绩, 0) AS 折算分 FROM 成绩表 ) AS t;这种细节网上很少讲透但写错了就是硬错误排查起来还挺容易让人蒙圈的。2.3 排名字段选什么int 和 bigint 的取舍在创建存储过程或临时表存储排名结果时排名列的数据类型我建议直接用INT。别觉得这是小事我见过有人在表设计里把排名列设成VARCHAR(10)结果排序时“10”排在“9”前面闹出过笑话。排名结果一般不会超过 21 亿INT完全足够而且和ROW_NUMBER()返回的类型一致后续做比较运算不用转型省掉一顿麻烦。3. 成绩排名的三大主力函数row_number、rank、dense_rank3.1 三个函数的语法与返回结果对比SQL Server 里做排名有三个现成的窗口函数ROW_NUMBER()、RANK()和DENSE_RANK()。它们长得像但行为差异非常关键尤其是遇到成绩相同的情况时。我用一组实际数据来演示。假设有 5 名学生成绩分别是 95、90、90、85、80学号成绩ROW_NUMBER()RANK()DENSE_RANK()A00195111A00290222A00390322A00485443A00580554从表里能清楚看到ROW_NUMBER()不管成绩是否相同强制给每一行分配一个不重复的序号序号连续不间断。RANK()成绩相同则名次相同但下一个名次会跳号。90 分两人并列第二下一名直接跳到第 4 名。DENSE_RANK()成绩相同则名次相同而且名次不跳号。90 分两人并列第二下一名是第 3 名。选择哪个函数取决于业务规则。如果是体育比赛的淘汰赛分组名次跳号不影响晋级名额用RANK()没问题如果只是普通成绩单展示希望名次连续用DENSE_RANK()更友好如果系统里每个学生的排名必须唯一比如排座位、生成唯一编号那就只能用ROW_NUMBER()。3.2 给排名函数加并行排序条件消除不确定性刚才那张表里ROW_NUMBER() 给 90 分的那两行随机分配了第 2 和第 3 名。实际上 SQL Server 会按照物理存储顺序分配看起来像“随机”。正式报表里这种不确定性是隐患比如两次跑出来的名次不一样业务方会质疑数据有问题。解决办法是加并列排序条件。比如成绩相同时按学号升序作为次级排序SELECT 学号, 姓名, 成绩, ROW_NUMBER() OVER(ORDER BY 成绩 DESC, 学号 ASC) AS 稳定排名 FROM 成绩表;这样成绩相同的学生学号在前的排在前面谁先谁后就是确定的了。这个操作我在做考试成绩归档时几乎每次都会加就是怕后续对账时发现排名对不上。3.3 并列排名在业务里的实际处理方式如果是考试发奖状“并列第二”是能说出口的直接用RANK()或DENSE_RANK()就行了。但如果这个排名要作为“按名次发放奖学金档位”的依据就得小心了。比如一等奖要求第 1 名二等奖 2-3 名三等奖 4-6 名用RANK()出来 5 个人并列第 2那一等奖之外的人到底算几等光靠 SQL 返回的名次没法直接判断。我常用的做法是先算出DENSE_RANK()作为并列名次展示再用ROW_NUMBER()生成一个内部使用的唯一序号用于奖励等级判断。一张表里同时保留两列一列给业务看一列给系统逻辑用各司其职谁都不得罪。4. 经典实战场景拆解排行榜、班级排名与前 N 名4.1 全校总分排行榜附带参与排名人数先来看最基础的需求全校所有学生按总分从高到低排一个总榜。这里要注意数据粒度的转换——每个学生可能有多科成绩得先按学生汇总总分再开窗排名。WITH StudentTotal AS ( SELECT 学号, SUM(成绩) AS 总分 FROM 成绩表 GROUP BY 学号 ) SELECT 学号, 总分, ROW_NUMBER() OVER(ORDER BY 总分 DESC) AS 名次, COUNT(*) OVER() AS 参与排名人数 FROM StudentTotal;这里用了两个窗口函数ROW_NUMBER()生成名次COUNT(*) OVER()返回整个结果集的行数。COUNT(*) OVER()不加PARTITION BY时作用于整个结果集效果等同于在每一行上附加一个总行数。这样报表页面上可以很直接地展示“本次考试共 328 人参与排名”这类信息不需要额外执行一条COUNT(*)查询。这个技巧在做报表看板的时候特别实用能省一次往返数据库的查询。需要注意的是这里的总计依赖GROUP BY先完成汇总窗口函数在分组结果上接着算顺序是对的。如果你想在窗口函数里直接对明细成绩做SUM()得到的是每个科目累加的结果跟总分排名不是一回事别混淆。4.2 班级内部排名看清同一张卷子在不同班的分布学校经常要做“班级排名”目的是看同一个老师教的不同班级之间同一次考试的成绩差异。这种场景下PARTITION BY 班级就派上大用场了。SELECT 班级, 学号, 姓名, 成绩, RANK() OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) AS 班级排名 FROM 成绩表 WHERE 科目 数学;这条语句的执行逻辑是先用WHERE把数学成绩过滤出来然后 SQL Server 按班级把数据划分成若干分区在每个分区内独立计算排名。理科班的第一名是 1文科班的第一名也是 1互不干扰。如果还要同时展示年级排名可以在同一句话里再加一列SELECT 班级, 学号, 姓名, 成绩, RANK() OVER(PARTITION BY 班级 ORDER BY 成绩 DESC) AS 班级排名, RANK() OVER(ORDER BY 成绩 DESC) AS 年级排名 FROM 成绩表 WHERE 科目 数学;一条 SQL 同时输出班级排名和年级排名这就是窗口函数叠加的威力。报表上两个排名一对比哪些学生在班级里靠前但年级里靠后、哪些学生在班里不起眼但在年级是黑马一眼就看出来了。这种需求在线下教学质量分析里非常常见用partition by写起来干净利落。4.3 按班级科目分组排名处理多维度排行榜再进一步业务要的是“每个班每一科的单科排名”。这就是多列PARTITION BY的问题。SELECT 班级, 科目, 学号, 姓名, 成绩, ROW_NUMBER() OVER( PARTITION BY 班级, 科目 ORDER BY 成绩 DESC ) AS 单科班级排名 FROM 成绩表;PARTITION BY后面可以跟多个列它们共同定义一个分区。这里(班级, 科目)作为一个整体分区键每个班级的每门科目都被视为独立的排名空间。SQL Server 会按这两个列的组合值对数据进行逻辑分组然后在每个组内独立排序和编号。执行计划里能看到一个专门的“Segment”运算符它负责识别分区边界。当分区键发生变化时SQL Server 会重置内部计数器重新从 1 开始编号。理解了这个机制你就明白了为什么窗口函数比自连接、子查询的方案性能更好——它只需要对数据做一次排序然后顺序扫描即可完成所有分区的排名计算。如果需求是“每个班级每科前 3 名”可以在上面基础上再套一层SELECT * FROM ( SELECT 班级, 科目, 学号, 姓名, 成绩, ROW_NUMBER() OVER( PARTITION BY 班级, 科目 ORDER BY 成绩 DESC ) AS 排名 FROM 成绩表 ) AS t WHERE 排名 3;注意窗口函数不能直接放在WHERE子句里因为窗口计算发生在WHERE过滤之后。必须用子查询包一层在外层再过滤排名 3。这个模式是“分组 Top N”问题的标准解法写多了你就熟了。4.4 平均分之上的“相对位置”如何找偏科生纯排名的价值有限业务方真正想看的往往是排名背后反映的问题。比如年级排名靠前但某科班级排名垫底的学生就是典型的“偏科生”。这类查询也是partition by的拿手好戏。WITH StudentStats AS ( SELECT 学号, 姓名, AVG(成绩) OVER(PARTITION BY 学号) AS 个人均分, 成绩, ROW_NUMBER() OVER( PARTITION BY 学号 ORDER BY 成绩 ASC ) AS 最弱科目序号 FROM 成绩表 ) SELECT 学号, 姓名, 个人均分, 成绩 AS 最弱科目成绩 FROM StudentStats WHERE 最弱科目序号 1;这里的关键思路是以学号为分区键对每个学生的多科成绩从小到大排列取出序号为 1 的那一科就是该学生的“最弱科目”。同时用AVG() OVER(PARTITION BY 学号)算出个人平均分一对比就能看出这个学生的短板科目比个人均分低了多少。这种写法避免了多次自连接一次扫描完成所有学生的个人画像在几百个学生、几千条成绩记录的场景下性能表现很不错。5. 高级实战移动平均、累计排名和第 N 名查找5.1 用窗口函数实现累计排名与分段统计排名除了“绝对名次”还有一种“相对名次”的玩法。比如想知道成绩排在前 30% 的学生有哪些直接用RANK()再除以总人数就行。WITH Ranked AS ( SELECT 学号, 成绩, RANK() OVER(ORDER BY 成绩 DESC) AS 名次, COUNT(*) OVER() AS 总人数 FROM 成绩表 ) SELECT 学号, 成绩, 名次, 总人数, CAST(名次 AS FLOAT) / 总人数 * 100 AS 百分比排名 FROM Ranked WHERE 名次 总人数 * 0.3;百分比排名在教育行业用得很多比如“这个学生超过了全校 85% 的同学”。SQL Server 也有专门的PERCENT_RANK()和CUME_DIST()窗口函数不过我觉得用RANK() / COUNT(*)这种基础写法更直观也更容易向业务解释。计算出百分比排名之后分段统计就顺理成章了——前 10% 是 A 档、10%-30% 是 B 档、30%-60% 是 C 档这种规则只需要再套一个CASE WHEN就能实现。5.2 查询每个班级的第 2 名OFFSET 窗口的妙用“找出每个班级第二名”这类需求传统的子查询写法非常啰嗦要处理并列情况更是头疼。用窗口函数 OFFSET就简单太多了。SELECT 班级, 学号, 成绩 FROM ( SELECT 班级, 学号, 成绩, ROW_NUMBER() OVER( PARTITION BY 班级 ORDER BY 成绩 DESC ) AS 班级排名 FROM 成绩表 ) AS t WHERE 班级排名 2;如果想取“每个班级第 2 到第 4 名”就改成WHERE 班级排名 BETWEEN 2 AND 4。想取“去掉第一名之后的前三人”先不考虑并列的话就是WHERE 班级排名 BETWEEN 2 AND 4跟上面一样。如果有并列成绩需要按业务规则决定是取RANK()还是ROW_NUMBER()前面讲过区分方法这里不重复。如果用的是 SQL Server 2012 以上的版本还有更优雅的写法——OFFSET 1 ROW FETCH NEXT 3 ROWS ONLY它可以在每个分区内跳过第一名再往下取 3 行。不过实际用下来多数报表场景直接用ROW_NUMBER() 外层WHERE过滤就足够清晰了OFFSET语法对不常用窗口函数的人来说反而费解。5.3 滚动排名近 3 次考试的平均名次成绩分析里还有一个常见需求看一个学生最近几次考试的综合表现趋势。用partition by配合ROWS BETWEEN窗口框架可以算移动平均值。WITH ExamOrder AS ( SELECT 学号, 考试时间, 成绩, ROW_NUMBER() OVER( PARTITION BY 学号 ORDER BY 考试时间 DESC ) AS 考试序号 FROM 成绩表 ) SELECT 学号, 考试时间, 成绩, AVG(成绩) OVER( PARTITION BY 学号 ORDER BY 考试序号 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS 近三次平均分 FROM ExamOrder;ROWS BETWEEN 2 PRECEDING AND CURRENT ROW的意思是窗口范围从当前行的前两行开始到当前行结束共 3 行的跨度。配合ORDER BY 考试序号实现的就是“最近 3 次考试的平均分”。这种滑动窗口的分析能力是传统 SQL 写法很难优雅实现的如果不用窗口函数你需要多层嵌套子查询加ROW_NUMBER()自连接代码量翻好几倍性能还更差。6. 性能调优与常见坑我亲手踩过的6.1 为什么你的窗口函数查询慢先看执行计划窗口函数的性能瓶颈主要在排序操作上。ORDER BY子句要求 SQL Server 对分区内的数据排序如果表数据量大且没有合适的索引这一步会产生大量的内存授予和临时库写入查询自然就慢了。有一回我处理一张 500 万行成绩表的班级排名查询第一次跑用了 20 多秒。打开执行计划看发现 Sort 运算符占了 80% 的成本。解决办法是给(班级, 成绩 DESC)建了一个复合索引再跑一次只需要 3 秒。因为PARTITION BY 班级和ORDER BY 成绩 DESC正好对应复合索引的前两列SQL Server 可以从索引里直接按顺序读取数据连排序都省了。索引设计上没有万能公式但有一个通用参考分区列放在索引前面排序列放在后面方向尽量和ORDER BY一致如果PARTITION BY是班级, 科目索引应该建(班级, 科目, 成绩 DESC)如果只是单纯的ORDER BY 成绩 DESC全局排名建(成绩 DESC)索引通常就够了建议每次写完窗口函数查询都打开“显示估计的执行计划”看看有没有 Sort 运算符、有没有 Table Scan这是性能排查的第一步。6.2 统计信息过时导致的排名错乱这是一个比较隐蔽的问题。如果表数据量变化剧烈但统计信息没更新查询优化器可能选择错误的执行策略返回的结果顺序也可能和预期不符。我遇到过一次某次月考后导入了新数据跑成绩排名时发现有两个学生的名次对调了排查了很久最后发现是统计信息的采样率太低导致的。解决方法是定期更新统计信息尤其是在批量导入数据后UPDATE STATISTICS 成绩表;更稳妥的做法是写一个维护计划每天凌晨对主要业务表执行UPDATE STATISTICS。这个操作开销不大但对查询性能的稳定性帮助非常大。成绩这类数据经常有大批量导入的场景导入后如果不更新统计信息SQL Server 依然按旧的数据分布来估算行数可能导致内存授予不足、排序落到 tempdb性能骤降。6.3 排序字符集和 NULL 值带来的名次差异成绩排序时如果字段类型是VARCHAR排序规则按字符字典序排。比如“9”会排在“82”后面因为字符 9 大于 8。这种问题在从 Excel 导入数据时特别容易遇到——明明存的是数字导入时被转成了文本。所以成绩表的成绩字段我强烈建议用DECIMAL(5,1)或DECIMAL(5,2)既支持小数又能保证排序正确。还有一个容易被忽略的坑是NULL值。SQL Server 默认排序里NULL被视为最小值ORDER BY 成绩 DESC时NULL会排在最后但ORDER BY 成绩 ASC时NULL会排在最前。如果你不希望NULL参与排名用WHERE 成绩 IS NOT NULL过滤掉如果必须保留又想强制NULL排在末尾可以加一个辅助排序列ORDER BY CASE WHEN 成绩 IS NULL THEN 1 ELSE 0 END, 成绩 DESC;这个CASE列让有成绩的永远排在前面没成绩的沉底。实测中这个写法在导出报表时非常常用避免了 NULL 排名混进名次序列里的尴尬。6.4 老版本 SQL Server 的兼容性注意事项虽然窗口函数从 2005 年就有了但有些更高级的写法是老版本不支持的。比如ROWS BETWEEN窗口框架是 2012 年才加入的OFFSET FETCH也是。如果你还在维护 SQL Server 2008 R2 的系统用ROWS BETWEEN会直接报语法错误。老版本的替代方案是自连接SELECT a.学号, AVG(b.成绩) AS 近三次平均分 FROM 成绩表 a JOIN 成绩表 b ON a.学号 b.学号 AND b.考试序号 BETWEEN a.考试序号 - 2 AND a.考试序号 GROUP BY a.学号;逻辑没错但性能差不少尤其数据量大时要反复扫描。所以有条件的话还是建议尽早升级到 2016 以上版本新版本不仅窗口函数更完善还有STRING_AGG等字符串聚合函数写报表轻松很多。7. 常用排名查询速查手册这里把我日常开发中最常用到的几类排名查询整理成一个速查表方便你直接抄作业需求场景推荐写法关键点全校排名无并列ROW_NUMBER() OVER(ORDER BY 成绩 DESC)名次唯一全校排名并列不跳号DENSE_RANK() OVER(ORDER BY 成绩 DESC)名次连续全校排名并列跳号RANK() OVER(ORDER BY 成绩 DESC)名次可能中断班级内排名RANK() OVER(PARTITION BY 班级 ORDER BY 成绩 DESC)分区独立排名班级科目排名ROW_NUMBER() OVER(PARTITION BY 班级, 科目 ORDER BY 成绩 DESC)多列分区每个班前3名子查询 WHERE 排名 3不能直接 WHERE 窗口函数每个班第2名子查询 WHERE 排名 2调整数字即可找最弱科目ROW_NUMBER() OVER(PARTITION BY 学号 ORDER BY 成绩 ASC)升序取出最小近3次平均分AVG() OVER(... ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)需要日期排序这张表覆盖了我在成绩类报表中遇到的最常见需求。实际业务可能还有一些变体比如“排除缺考后排名”、“只排及格学生”本质上都是在窗口计算之前先用WHERE做好数据筛选然后再套上面这些写法逻辑是一样的。8. 两个容易被忽略的细节技巧第一个是窗口函数不要和DISTINCT混用。如果对窗口结果做DISTINCTSQL Server 会先计算窗口函数再去除重复行这不仅完全没必要还可能造成语义偏差。比如你SELECT DISTINCT 学号, ROW_NUMBER() OVER(ORDER BY 成绩 DESC)因为每一行的窗口值不同DISTINCT根本不会去重任何行白白浪费资源。遇到这种情况先想想是不是应该用GROUP BY而不是用DISTINCT硬凑。第二个是在存储过程里做排名时尽量用表变量或临时表把排名结果物化。原因很简单如果你在一个存储过程里多次引用窗口函数的计算结果SQL Server 会重复执行相同的窗口计算。虽然执行计划可能会缓存但多一层临时表可以让数据流更清晰也方便后续调试。我习惯把排名结果先SELECT ... INTO #temp存进临时表后面所有过滤、排序、展示都基于临时表操作代码可读性大幅提升排查问题也简单。关于临时表还要提醒一句如果临时表的数据量超过 5000 行记得在临时表上建索引。否则后面关联其他表时临时表会被反复扫描性能会很难看。我在一个日常跑批的存储过程里因为一张 2 万行的临时表没建索引导致整个批处理从 10 分钟变成 40 分钟加上索引后秒回。这个坑可以说教训深刻。9. 写在最后的一点个人体会做了这些年数据库开发我越来越觉得窗口函数是 SQL 能力的分水岭。会写partition by的人和只会GROUP BY的人面对同样一个“成绩排名”需求写出来的代码量可能是 20 行和 80 行的区别可读性和性能差距更是天壤之别。partition by的价值不在于它多复杂而在于它给了你一种“在明细行上做分析”的思维方式让你不用再费劲地用自连接、子查询去模拟本来很简单的逻辑。这篇实战里所有的示例都是我实际上线过或验证过的写法你可以直接拿过去跑。成绩排名只是partition by的一个切入点理解了分区、排序、窗口范围这三个核心概念之后你会发现它能做的事情远比“排名”本身要广得多——移动平均、同比环比、占比重计算全是同一套思维。希望这篇能帮你少走一些我当年走过的弯路。
