尧图网页设计 
1. 项目背景与核心思路拆解1.1 成绩排名这个需求十有八九你会遇见先说个我自己的经历。早些年在一家教育培训机构做数据支持每周都要出一份学员成绩汇总表。需求方提的要求很普通“按班级看看每个学生的总分排名顺便把单科排名也带上”。乍一听很简单但真要写SQL的时候才发现传统写法不是不行只是写出来的SQL又长又绕一个排名需求要套好几层子查询跑起来还慢改起来更是头疼。后来换成了partition by窗口函数整个报表只用一条语句就搞定了性能还提升了不止一个档次。可能有人会觉得排名不就是ORDER BY一下嘛如果真的只是全表排序那确实简单。但“排名”和“排序”有个本质区别排序只是把数据按大小排好排名要在排序的基础上给出“第几名”这个序号而且业务上经常要求分组排名——比如按班级排名、按科目排名、按年级排名。这种分组排名需求用传统SQL写起来非常痛苦但交给partition by窗口函数几行代码就出来了。这篇博文是partition by函数实战系列的第三篇前两篇我们聊了窗口函数的基础语法和聚合函数配合使用的方式这篇就聚焦在“成绩排名”这个高频业务场景上。适合谁看刚接触窗口函数、想搞懂ROW_NUMBER、RANK、DENSE_RANK区别的新手以及写了不少SQL但每次排名都靠子查询硬怼、想换个优雅写法的老手都应该读一读。1.2 传统排名写法为什么让人抓狂在介绍partition by的写法之前我先带大家回顾一下传统排名的写法这样才能体会窗口函数到底解决了什么问题。假设有一个成绩表StudentScore字段有学生ID、姓名、班级、科目、分数。要算“每个学生在班级内的总分排名”传统思路是先按班级学生聚合出总分然后对每个学生去查“班里有多少人总分比我高”有多少人比自己高自己的名次就是“比我高的人数1”。写出来大概是这个样子SELECT s1.StudentName, s1.ClassName, s1.TotalScore, ( SELECT COUNT(DISTINCT s2.StudentID) 1 FROM ( SELECT StudentID, ClassName, SUM(Score) AS TotalScore FROM StudentScore GROUP BY StudentID, ClassName ) s2 WHERE s2.ClassName s1.ClassName AND s2.TotalScore s1.TotalScore ) AS ClassRank FROM ( SELECT StudentID, StudentName, ClassName, SUM(Score) AS TotalScore FROM StudentScore GROUP BY StudentID, StudentName, ClassName ) s1;这段代码的问题很明显嵌套层级深子查询套子查询可读性非常差别人接手你的代码得看半天才能明白逻辑。计算量大对每一行都要执行一次子查询去统计比自己分高的人数属于典型的“逐行扫描”数据量一上来性能就崩。并列排名处理麻烦如果用COUNT(DISTINCT)处理并列情况逻辑更绕不处理并列名次又会出问题。扩展性差今天要“班级内排名”明天要“科目前三”后天要“年级排名”每个新需求都要重写一套子查询。说实话我当年用这种写法维护成绩报表的时候内心是崩溃的。每次需求稍微变一下整个查询就要重构还特别容易写错。直到后来接触到窗口函数才真正体会到什么叫“写法降维打击”。1.3 窗口函数解决问题的底层逻辑窗口函数之所以能把排名写得这么简洁核心就在于它和GROUP BY有着本质不同的处理方式。我用一个比较生活化的比喻来解释。想象你手头有一堆扑克牌每张牌上面有不同的花色和点数。GROUP BY的做法是把同花色的牌收拢成一叠每叠只报一个汇总信息比如这叠牌的总点数你再也看不到单张牌了。而窗口函数PARTITION BY的做法是仍然把所有牌摊在桌面上只是按花色给每张牌贴上一个分组标签然后在这个分组范围内做排序、编号、汇总每张牌本身依然保留着完整信息。这就是窗口函数最关键的特性分组但不折叠行。对于成绩排名这种业务来说太合适了因为我们既想看排名结果又想看到每个学生的具体成绩两者可以同时出现在结果集里不需要再做一次关联查询。PARTITION BY指定的分组字段决定了“在谁的范围内做计算”。比如按班级分组排名就是在班级范围内计算按班级科目分组排名就是在“某个班某门课”的范围内计算不加PARTITION BY就是整个结果集当作一个大组算出的是全校排名。另外一个容易被忽略的点是窗口函数在SQL语句中的执行顺序它发生在WHERE、GROUP BY、HAVING之后但在最终的ORDER BY之前。这意味着你可以在窗口函数里使用经过WHERE过滤后的数据但不能再在WHERE里引用窗口函数的结果这也是很多人踩坑的地方后面我会专门讲。2. 三大排名函数拆解考试排名的三种打开方式2.1 ROW_NUMBER最朴素的流水号排名先把最基础的ROW_NUMBER()讲透。它的逻辑最简单在分区内按指定顺序依次编号从1开始连续递增不存在并列。比如按分数降序排名两个人都是85分ROW_NUMBER()会给其中一个人编1号另一个人编2号谁前谁后由ORDER BY后面的辅助条件决定。SELECT StudentName, ClassName, Score, ROW_NUMBER() OVER ( PARTITION BY ClassName ORDER BY Score DESC, StudentID ASC ) AS RowNumRank FROM StudentScore WHERE SubjectName 数学;这个函数适合什么场景我举几个真实的业务例子行政班学号管理每个班学生需要一个唯一的序号不关心分数高低重叠只关心“排下来是第几个”。分页查询ROW_NUMBER()配合BETWEEN取某几行是SQL Server里做分页的经典做法。去重按某个维度分组后只保留每组第1条记录可以配合ROW_NUMBER()实现。它不处理并列这是设计如此不是缺陷。在我做过的很多报表里ROW_NUMBER()其实是使用频率最高的排名函数原因也很简单它生成的序号唯一、连续下游做透视表、画图表、做筛选都对这列序号特别友好。特别提醒一下ROW_NUMBER()的ORDER BY里如果只写了分数而同分的人又很多那么每次查询出来的顺序可能不一样。如果你希望“同分时按学号从小到大排”就必须在ORDER BY里把学号加上。这一点在实际工作中非常实用比如按成绩排名次的同时希望同分的学生按姓名笔画排序直接多写一个排序条件就行。2.2 RANK有并列就跳号的“奖学金排名”RANK()是为“允许并列但并列会占名额”的业务场景设计的。它的规则是遇到分数相同的人给相同的名次但下一个名次会跳过。比如两个人并列第1下一个就是第3没有第2名。在成绩排名场景里RANK()对应的是“奖学金、保送名额”这类逻辑假如一等奖学金只有1个名额出现两个并列第一那么名额其实是给出去2个第三名虽然成绩也很高但因为名额不够只能落到二等。用一个跳号的名次恰恰如实反映了这种“占了名额”的关系。SELECT StudentName, ClassName, Score, RANK() OVER ( PARTITION BY ClassName ORDER BY Score DESC ) AS RankResult FROM StudentScore WHERE SubjectName 数学;我做过一个学科竞赛的排名需求主办方要求必须用RANK()原因就是“并列的名次必须空出来后面的人名次按实际人数顺延”这样才能准确反映获奖档位的实际比例。如果你误用DENSE_RANK()会导致第三名算到第二档里去整个获奖名单对不上。2.3 DENSE_RANK不跳号的“等级排名”DENSE_RANK()的规则是并列名次相同但下一个名次不跳号。两个人并列第1下一个就是第2。它反映的是“按分数段划分等级”的思路。什么场景用DENSE_RANK()最常见的是等级测评。比如把学生成绩分成优、良、合格、不合格四个档次90分以上是优80-90是良。这时候用DENSE_RANK()得到的“第1档、第2档”就很契合等级概念跳不跳号反而不重要。还有体育比赛的“名次并列则共享奖牌”的规则也是DENSE_RANK()的语义。SELECT StudentName, ClassName, Score, DENSE_RANK() OVER ( PARTITION BY ClassName ORDER BY Score DESC ) AS DenseRankResult FROM StudentScore WHERE SubjectName 数学;2.4 三个函数的输出对比一张表说明白我把三个函数在相同数据下的输出放在一起对比这样大家一眼就能看出区别。假设某个班级数学成绩如下张三85、李四85、王五80、赵六78、钱七78、孙八70。学生分数ROW_NUMBERRANKDENSE_RANK张三85111李四85211王五80332赵六78443钱七78543孙八70664注意几处容易混淆的细节ROW_NUMBER()中的1、2并非“并列第一的意思”只是“排在前面的两条记录”谁先谁后取决于ORDER BY里的辅助字段。RANK()中85分占掉了名次1和2所以王五虽然只比并列第一低5分名次却直接掉到了3。DENSE_RANK()中两个85分都是第1王五就是第2中间没有空档。实际项目里这些语义直接决定了报表能不能对接业务规则。我一般建议开发者在写排名SQL之前先和业务确认清楚一个问题“并列的人下一个名次要不要跳号”。就这一句话就能确定该用RANK()还是DENSE_RANK()。3. 成绩排名实战从建表到完整查询3.1 建表与造数据为了让后面的案例能直接复现我先给出一张完整的建表语句和测试数据。表结构按实际业务设计不搞太复杂的范式够用就好。CREATE TABLE StudentScore ( StudentID INT NOT NULL, StudentName NVARCHAR(50) NOT NULL, ClassName NVARCHAR(20) NOT NULL, SubjectName NVARCHAR(20) NOT NULL, Score INT NULL, ExamDate DATE NULL );字段说明StudentID学生IDStudentName姓名ClassName班级SubjectName科目Score分数允许空值用来模拟缺考情况ExamDate考试日期。日期字段是为后面的进阶案例准备的。插入演示数据INSERT INTO StudentScore (StudentID, StudentName, ClassName, SubjectName, Score, ExamDate) VALUES (1, N张三, N一班, N数学, 85, 2024-11-01), (2, N李四, N一班, N数学, 85, 2024-11-01), (3, N王五, N一班, N数学, 80, 2024-11-01), (4, N赵六, N一班, N数学, 78, 2024-11-01), (5, N钱七, N一班, N数学, 78, 2024-11-01), (6, N孙八, N一班, N数学, 70, 2024-11-01), (7, N周九, N二班, N数学, 92, 2024-11-01), (8, N吴十, N二班, N数学, 88, 2024-11-01), (9, N郑十一, N二班, N数学, 75, 2024-11-01), (1, N张三, N一班, N语文, 78, 2024-11-01), (2, N李四, N一班, N语文, 90, 2024-11-01), (3, N王五, N一班, N语文, 85, 2024-11-01), (4, N赵六, N一班, N语文, 82, 2024-11-01), (5, N钱七, N一班, N语文, 65, 2024-11-01), (6, N孙八, N一班, N语文, 88, 2024-11-01), (7, N周九, N二班, N语文, 95, 2024-11-01), (8, N吴十, N二班, N语文, 92, 2024-11-01), (9, N郑十一, N二班, N语文, 78, 2024-11-01);这里有9个学生两个班两门课。为什么特意造两个班级的数据因为做分组排名测试数据必须跨组否则看不出PARTITION BY的作用。3.2 基础排名按班级对数学成绩排名需求查出所有学生的数学成绩并给出班级内的名次同时显示全班排名和全校排名。这次我直接把ROW_NUMBER、RANK、DENSE_RANK三个函数都放进来让大家看同一份数据的输出对比SELECT ClassName, StudentName, Score, ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS RowNumRank, RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS RankResult, DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS DenseRankResult FROM StudentScore WHERE SubjectName 数学 ORDER BY ClassName, Score DESC;查询结果如下部分截取班级姓名分数RowNumRankRankResultDenseRankResult一班张三85111一班李四85211一班王五80332一班赵六78443一班钱七78543一班孙八70664二班周九92111二班吴十88222二班郑十一75333看到没这就是窗口函数和普通ORDER BY最大的区别名次是分组内重新从1开始的。一班的张三李四都是班级第1二班的周九也是班级第1。如果只用ORDER BY Score DESC你只能得到整体排序得不到“各班的第1名是谁”这个信息。3.3 多维度分组班级科目同时排名实际业务里很少只排数学一科通常要“每科都排名”。有人会想那我加WHERE SubjectName 数学查一次再改条件查一次当然可以但如果你希望一张表里同时看到所有学生的所有科目排名就得在PARTITION BY里同时指定多个字段。SELECT ClassName, SubjectName, StudentName, Score, ROW_NUMBER() OVER ( PARTITION BY ClassName, SubjectName ORDER BY Score DESC ) AS RankInClassAndSubject, RANK() OVER ( PARTITION BY ClassName, SubjectName ORDER BY Score DESC ) AS RankWithGap FROM StudentScore ORDER BY ClassName, SubjectName, Score DESC;这里的逻辑很有意思。PARTITION BY ClassName, SubjectName的含义是把班级和科目都相同的行分为一组然后在组内排名。效果就是“一班语文组内排名”、“二班数学组内排名”互不干扰。相比把全校所有人放在一起排序这种分组排名的粒度更细也更接近真实教务需求。如果把需求改成“只看全校单科排名不分班级”那就去掉PARTITION BY直接ORDER BY Score DESCSELECT ClassName, SubjectName, StudentName, Score, RANK() OVER (ORDER BY Score DESC) AS SchoolRank FROM StudentScore WHERE SubjectName 数学;注意这里OVER()里没写PARTITION BY表示整个结果集是一组排名就是全校排名。这也是容易忽略的细节。3.4 总分排名是怎么做的刚才举的例子都是单科排名。很多需求是按“总分”来排名。总分不是表里直接有的字段得先聚合。这里有个常见的坑不能在窗口函数里直接写SUM(Score)当成字段去排名得分步骤来。推荐的做法是先用GROUP BY把总分算出来再用窗口函数排名。两种写法都可行写法一用子查询SELECT StudentName, ClassName, TotalScore, RANK() OVER (PARTITION BY ClassName ORDER BY TotalScore DESC) AS ClassRank FROM ( SELECT StudentName, ClassName, SUM(Score) AS TotalScore FROM StudentScore GROUP BY StudentName, ClassName ) AS t ORDER BY ClassName, TotalScore DESC;写法二用CTEWITH StudentTotal AS ( SELECT StudentName, ClassName, SUM(Score) AS TotalScore FROM StudentScore GROUP BY StudentName, ClassName ) SELECT StudentName, ClassName, TotalScore, RANK() OVER (PARTITION BY ClassName ORDER BY TotalScore DESC) AS ClassRank FROM StudentTotal ORDER BY ClassName, TotalScore DESC;我个人更建议用CTE理由有两个一是可读性好逻辑步骤清晰二是后续如果要继续做过滤比如只要各班前3名CTE可以直接往下接比层层嵌套的子查询好维护得多。4. 进阶场景TopN、并列策略与成绩波动分析4.1 查每班前三名经典TopN问题“每个班级取总分前三名”几乎是成绩排名需求里出现频率最高的也是窗口函数最能体现优势的场景。这里用ROW_NUMBER()实现。WITH StudentTotal AS ( SELECT StudentName, ClassName, SUM(Score) AS TotalScore FROM StudentScore GROUP BY StudentName, ClassName ), RankedStudents AS ( SELECT StudentName, ClassName, TotalScore, ROW_NUMBER() OVER ( PARTITION BY ClassName ORDER BY TotalScore DESC, StudentName ASC ) AS Seq FROM StudentTotal ) SELECT ClassName, StudentName, TotalScore, Seq FROM RankedStudents WHERE Seq 3 ORDER BY ClassName, Seq;为什么这里用ROW_NUMBER()而不是RANK()因为业务上通常说的“每班前三名”隐含了一个前提名额只有3个。如果第3名和第4名同分ROW_NUMBER()会硬排出一个3和一个4最终选出3个人而RANK()会把并列的都算进去可能选出4个人。到底哪个对取决于需求。我一般会先问一句“同分要并列吗”然后再决定用哪个函数。在不确定的情况下代码里注释写清楚“此处使用ROW_NUMBER同分不并列如需并列请改用RANK/DENSE_RANK”给后来接手的人省很多沟通成本。4.2 同分保留并列名次的做法如果业务要求“同分必须同名次”那么TopN查询就需要换策略。比如“每班排名前20%的学生进入复试”此时并列必须保留再用ROW_NUMBER()就不合适了。一种处理方法是先用DENSE_RANK()算出并列名次再以名次作为筛选条件。比如取每班前3个名次WITH StudentTotal AS ( SELECT StudentName, ClassName, SUM(Score) AS TotalScore FROM StudentScore GROUP BY StudentName, ClassName ), RankedStudents AS ( SELECT StudentName, ClassName, TotalScore, DENSE_RANK() OVER ( PARTITION BY ClassName ORDER BY TotalScore DESC ) AS DenseRank FROM StudentTotal ) SELECT ClassName, StudentName, TotalScore, DenseRank FROM RankedStudents WHERE DenseRank 3 ORDER BY ClassName, DenseRank;注意这个写法会输出“前3个名次”的所有人即使某个名次有5个人并列也会全部输出。这在实际业务中常常能给人惊喜你以为只录取9个人结果因为并列实际录取了12个人。遇到这种情况不能怪SQL只能怪业务当初没把并列规则说清楚。作为开发者的职责是把规则的差异提前暴露出来让业务方做决策。4.3 一次查询同时出多个维度的排名实际报表中经常要“既看班级排名又看全校排名”。这在以前是两套SQL拼表用窗口函数可以在一条SQL里同时完成。SELECT ClassName, StudentName, SubjectName, Score, RANK() OVER ( PARTITION BY ClassName, SubjectName ORDER BY Score DESC ) AS ClassSubjectRank, RANK() OVER ( PARTITION BY SubjectName ORDER BY Score DESC ) AS SchoolSubjectRank FROM StudentScore WHERE SubjectName 数学 ORDER BY ClassName, Score DESC;查询结果班级姓名科目分数ClassSubjectRankSchoolSubjectRank一班张三数学8512一班李四数学8512一班王五数学8034二班周九数学9211二班吴十数学8823这个查询很有意思同一个RANK()函数PARTITION BY的粒度不同算出来的名次含义就完全不同。张三在班内并列第1放到全校数学排名就只有并列第2了因为二班周九的92分更高。一眼看去班级排名和学校排名的差异就暴露出来了。这种“同屏对比”对老师分析班级教学质量特别有用。4.4 窗口聚合与成绩趋势分析排名之外还有一种常见需求算班级平均分、最高分、每个学生和平均分的差距。这些聚合值也可以用窗口函数直接引用。SELECT ClassName, StudentName, SubjectName, Score, AVG(Score) OVER (PARTITION BY ClassName, SubjectName) AS ClassAvgScore, MAX(Score) OVER (PARTITION BY ClassName, SubjectName) AS ClassMaxScore, Score - AVG(Score) OVER (PARTITION BY ClassName, SubjectName) AS DiffFromAvg FROM StudentScore WHERE SubjectName 数学 ORDER BY ClassName, Score DESC;这里AVG(Score) OVER(...)对每一行都返回“所属分组的平均分”不需要再额外写一个子查询去关联。这个特性在做成绩分析报表时非常实用你可以直接在明细表旁边加一列“和班级平均分的差距”不用先聚合再关联一条SQL就搞定。如果要分析“一个学生连续多次考试的成绩波动”可以引入LAG()滞后函数配合窗口排序。比如查每个学生在历次数学考试中的分数变化SELECT StudentName, ExamDate, Score, LAG(Score, 1) OVER ( PARTITION BY StudentName, SubjectName ORDER BY ExamDate ) AS PreviousScore, Score - LAG(Score, 1) OVER ( PARTITION BY StudentName, SubjectName ORDER BY ExamDate ) AS ScoreChange FROM StudentScore WHERE SubjectName 数学 ORDER BY StudentName, ExamDate;LAG(Score, 1)的意思是取当前行的前一行按ORDER BY ExamDate排序后的分数。这样就能很直观地看到某学生上次考了多少、这次考了多少、涨了还是跌了。配合前面的排名函数你甚至可以算“名次变化”即上次和这次在班内的排名差用于追踪学生的学习状态。这些组合用法是传统GROUP BY很难实现的。5. 常见问题与排查技巧实录5.1 忘了PARTITION BY还是忘了ORDER BY窗口函数有个硬性要求排名函数必须有ORDER BY否则直接报错。SQL Server会提示“窗口函数不允许没有ORDER BY”。这个报错其实是在帮我们排名这个概念本身就是建立在一个明确的排序规则之上的没有顺序哪来的名次常见的错误写法-- 错误没有ORDER BY ROW_NUMBER() OVER (PARTITION BY ClassName) -- 正确 ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY Score DESC)我见过不少同事在初学阶段反复栽在这个细节上。解决的办法就是把窗口函数的三段式写全OVER括号里先问自己三个问题——按什么分组按什么排序要不要限定行范围分组对应PARTITION BY排序对应ORDER BY范围对应ROWS BETWEEN。三个问题都回答了语法就不会错。5.2 排名结果和预期不同大概率是ORDER BY方向错了排名默认是升序排序也就是数值越小名次越靠前。成绩排名当然要分数越高名次越靠前所以必须写ORDER BY Score DESC。写反成ORDER BY Score ASC结果就是“分数最低的人排第1”导出报表的时候才发现问题那场面是真的尴尬。-- 反例分数最低的排第1 RANK() OVER (PARTITION BY ClassName ORDER BY Score ASC) -- 正例分数最高的排第1 RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC)这里我分享一个检查技巧写完排名SQL后先别急着看全部结果取一个分组的前两条数据人工比对一下。比如查一班数学排名看一下第1名的分数是不是班里最高的。用常识去验证SQL结果比盯着代码逻辑检查快得多。5.3 为了排名去用GROUP BY把明细搞丢了很多新手会混淆GROUP BY和PARTITION BY最典型的现象是用了GROUP BY之后查询结果的行数变少了原本每个学生的明细都看不见了。这是因为GROUP BY把多行折叠成一行汇总失去明细是它的设计特点。-- 错误示范想排名结果连明细都没了 SELECT ClassName, StudentName, Score, RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC) FROM StudentScore GROUP BY ClassName, StudentName, Score;这段SQL能跑但结果集被聚合成“每个学生每个分数一行”如果同一个学生有多条记录比如多次考试聚合会把数据压扁排名自然就不对了。正确做法是需要明细就用PARTITION BY需要汇总就用GROUP BY两者可以直接结合使用——先用GROUP BY算汇总值再用窗口函数对汇总值排名前面总分示例里就是这么处理的。5.4 WHERE里直接用排名列报“列名无效”一个特别常见的错误想筛选“班级排名前3的学生”直接在WHERE里写排名函数。-- 错误WHERE里不能直接用窗口函数结果 SELECT ClassName, StudentName, Score, RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS RankResult FROM StudentScore WHERE RankResult 3;这样写会报错因为窗口函数在WHERE之后才计算WHERE执行时根本看不到RankResult这列。这个限制不只在SQL Server在所有主流数据库里都一样因为这是SQL标准的执行顺序决定的。正确做法是先把排名算出来作为子查询或CTE然后在外面包一层WHERE过滤WITH Ranked AS ( SELECT ClassName, StudentName, Score, RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS RankResult FROM StudentScore WHERE SubjectName 数学 ) SELECT * FROM Ranked WHERE RankResult 3;道理很简单窗口函数的结果是“计算完才有的新列”要在这个结果上过滤就必须让它先存在所以需要一层包裹。养成“写窗口函数就想到包一层CTE”的习惯这类错误能减少九成。5.5 同分排名到底算并列还是不算并列这个问题在业务层面非常关键但很多人在写SQL之前没跟业务确认。我在前面反复强调这点是因为教训太深刻了。一次我带团队做一份“星级教师评定”规则是“按学生满意度评分排名前10名评为五星”。结果因为用了ROW_NUMBER()而不是DENSE_RANK()第10名处恰好有两个同分的人系统硬把其中一个人排到第11名直接落选。事后复盘业务方说“同分当然应该并列”白纸黑字的规则里却没写清这一条。所以我的建议是写排名SQL之前先问需求方三句话同分的人名次要并列吗并列之后后面的名次要跳号吗如果名额有限比如前10名并列导致超过10个人怎么办这三个问题问完用哪个函数基本就定了不要并列用ROW_NUMBER()要并列且跳号用RANK()要并列且不跳号用DENSE_RANK()。把这三句话问明白能省掉后面大量的返工和扯皮。5.6 数据量大时排名查询慢怎么优化成绩表数据量很容易做大。一所学校几千个学生每次考试几十万条记录排名SQL如果写得不好报表跑几分钟出不来很正常。我总结了几条实用的优化经验为分区和排序字段建立索引。PARTITION BY和ORDER BY用到的字段比如ClassName、SubjectName、ExamDate、Score组合索引对窗口函数性能提升非常明显。SQL Server的窗口函数排序往往是内存中的排序操作如果数据能通过索引预排序开销会小很多。提前用WHERE过滤数据。窗口函数是在WHERE过滤之后才执行的所以能提前缩小数据集就一定先过滤。比如“只看本学期数据”先WHERE ExamDate 2024-09-01再排名数据量能少一大截。避免在窗口函数ORDER BY里用表达式。ORDER BY YEAR(ExamDate)这种写法会阻止索引利用每次都要算一遍表达式再排序。如果确实需要按年排序提前算好一列ExamYear存到表里会更快。用临时表分步处理。当SQL特别复杂、嵌套特别深时我倾向于先跑一个中间结果到临时表或表变量再对中间结果做排名。虽然多一步但每步的逻辑简单清晰执行计划也更稳定排查问题也容易得多。5.7 常见问题速查表我整理了一张速查表把成绩排名场景里最容易踩的坑都列出来。问题表现解决方案忘记写ORDER BY报错“窗口函数需要ORDER BY”排名函数必须写ORDER BY指定排序规则ORDER BY方向写反最低分排第1成绩排名用ORDER BY Score DESC在WHERE中用排名列报错“列名无效”包一层CTE或子查询再过滤GROUP BY折叠明细行数变少排名错乱明细排名用PARTITION BY不要用GROUP BY同分处理不符合预期名次出现断档或不断档先确认业务规则再选RANK/DENSE_RANK数据量大查询慢报表跑几分钟建索引、先过滤、避免ORDER BY表达式5.8 一个小众但有用的排查技巧最后分享一个排查技巧属于我自己的独门经验。当排名结果看起来不对又说不清哪里错的时候我会把窗口函数的PARTITION BY字段拼到SELECT里一起输出。比如排查“一班数学排名不对”时把ClassName、SubjectName、Score都列出来对照着肉眼检查。很多时候问题出在数据本身Score里有NULL值排序时NULL会被放在最前面还是最后面取决于数据库设置SQL Server里ASC排序NULL在前DESC排序NULL在最后这在成绩排名里就导致“缺考的人排到了最后”如果业务上希望缺考不计入排名那要注意提前过滤-- 排除缺考Score IS NULL后排名 SELECT ClassName, StudentName, Score, RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS RankResult FROM StudentScore WHERE SubjectName 数学 AND Score IS NOT NULL ORDER BY ClassName, RankResult;还有一种情况是StudentName存在同名不同人的问题一旦按姓名分组排名两个人的成绩会被揉到一起。这种问题靠看SQL代码是看不出来的只有把分组字段列出来肉眼验数据才能发现。所以我的习惯是任何窗口函数排完名先输出一遍分组字段排序列人工抽查几行再交到业务手里。排查问题的效率往往就体现在这种小习惯上。写在最后的一点经验成绩排名这个需求看起来简单但真做起来涉及的细节非常多。我用partition by函数做过很多次成绩报表之后最大的感受是窗口函数真正改变了写SQL的思维方式。以前遇到排名需求第一反应是“要不要建临时表”“子查询怎么嵌套”现在第一反应是“这个排名的分组粒度是什么、并列规则是什么”想清楚这两点SQL写起来就顺了。另外有人说RANK()用得最多但我实际项目里反而觉得ROW_NUMBER()的使用频率更高因为业务上更多是“取固定数量的人”——前三名、前10名、前20%这时候ROW_NUMBER()直接配合WHERE Seq N就完了。真正用RANK()和DENSE_RANK()的场景往往伴随着复杂的并列规则和名额分配需要跟业务反复确认。写代码前多问一句“同分怎么算”省下来的精力够你多写好几条SQL的。如果你手里的业务场景也涉及成绩排名建议先拿本文的示例数据把三个排名函数都跑一遍感受一下输出差异再套到自己真实的数据表上。熟练之后你会发现partition by能做的远不止排名它和聚合函数、滞后函数配合起来几乎能覆盖成绩分析里九成以上的需求。 SQL窗口函数PARTITION BY实战：三大排名函数实现成绩分组排名
1. 项目背景与核心思路拆解1.1 成绩排名这个需求十有八九你会遇见先说个我自己的经历。早些年在一家教育培训机构做数据支持每周都要出一份学员成绩汇总表。需求方提的要求很普通“按班级看看每个学生的总分排名顺便把单科排名也带上”。乍一听很简单但真要写SQL的时候才发现传统写法不是不行只是写出来的SQL又长又绕一个排名需求要套好几层子查询跑起来还慢改起来更是头疼。后来换成了partition by窗口函数整个报表只用一条语句就搞定了性能还提升了不止一个档次。可能有人会觉得排名不就是ORDER BY一下嘛如果真的只是全表排序那确实简单。但“排名”和“排序”有个本质区别排序只是把数据按大小排好排名要在排序的基础上给出“第几名”这个序号而且业务上经常要求分组排名——比如按班级排名、按科目排名、按年级排名。这种分组排名需求用传统SQL写起来非常痛苦但交给partition by窗口函数几行代码就出来了。这篇博文是partition by函数实战系列的第三篇前两篇我们聊了窗口函数的基础语法和聚合函数配合使用的方式这篇就聚焦在“成绩排名”这个高频业务场景上。适合谁看刚接触窗口函数、想搞懂ROW_NUMBER、RANK、DENSE_RANK区别的新手以及写了不少SQL但每次排名都靠子查询硬怼、想换个优雅写法的老手都应该读一读。1.2 传统排名写法为什么让人抓狂在介绍partition by的写法之前我先带大家回顾一下传统排名的写法这样才能体会窗口函数到底解决了什么问题。假设有一个成绩表StudentScore字段有学生ID、姓名、班级、科目、分数。要算“每个学生在班级内的总分排名”传统思路是先按班级学生聚合出总分然后对每个学生去查“班里有多少人总分比我高”有多少人比自己高自己的名次就是“比我高的人数1”。写出来大概是这个样子SELECT s1.StudentName, s1.ClassName, s1.TotalScore, ( SELECT COUNT(DISTINCT s2.StudentID) 1 FROM ( SELECT StudentID, ClassName, SUM(Score) AS TotalScore FROM StudentScore GROUP BY StudentID, ClassName ) s2 WHERE s2.ClassName s1.ClassName AND s2.TotalScore s1.TotalScore ) AS ClassRank FROM ( SELECT StudentID, StudentName, ClassName, SUM(Score) AS TotalScore FROM StudentScore GROUP BY StudentID, StudentName, ClassName ) s1;这段代码的问题很明显嵌套层级深子查询套子查询可读性非常差别人接手你的代码得看半天才能明白逻辑。计算量大对每一行都要执行一次子查询去统计比自己分高的人数属于典型的“逐行扫描”数据量一上来性能就崩。并列排名处理麻烦如果用COUNT(DISTINCT)处理并列情况逻辑更绕不处理并列名次又会出问题。扩展性差今天要“班级内排名”明天要“科目前三”后天要“年级排名”每个新需求都要重写一套子查询。说实话我当年用这种写法维护成绩报表的时候内心是崩溃的。每次需求稍微变一下整个查询就要重构还特别容易写错。直到后来接触到窗口函数才真正体会到什么叫“写法降维打击”。1.3 窗口函数解决问题的底层逻辑窗口函数之所以能把排名写得这么简洁核心就在于它和GROUP BY有着本质不同的处理方式。我用一个比较生活化的比喻来解释。想象你手头有一堆扑克牌每张牌上面有不同的花色和点数。GROUP BY的做法是把同花色的牌收拢成一叠每叠只报一个汇总信息比如这叠牌的总点数你再也看不到单张牌了。而窗口函数PARTITION BY的做法是仍然把所有牌摊在桌面上只是按花色给每张牌贴上一个分组标签然后在这个分组范围内做排序、编号、汇总每张牌本身依然保留着完整信息。这就是窗口函数最关键的特性分组但不折叠行。对于成绩排名这种业务来说太合适了因为我们既想看排名结果又想看到每个学生的具体成绩两者可以同时出现在结果集里不需要再做一次关联查询。PARTITION BY指定的分组字段决定了“在谁的范围内做计算”。比如按班级分组排名就是在班级范围内计算按班级科目分组排名就是在“某个班某门课”的范围内计算不加PARTITION BY就是整个结果集当作一个大组算出的是全校排名。另外一个容易被忽略的点是窗口函数在SQL语句中的执行顺序它发生在WHERE、GROUP BY、HAVING之后但在最终的ORDER BY之前。这意味着你可以在窗口函数里使用经过WHERE过滤后的数据但不能再在WHERE里引用窗口函数的结果这也是很多人踩坑的地方后面我会专门讲。2. 三大排名函数拆解考试排名的三种打开方式2.1 ROW_NUMBER最朴素的流水号排名先把最基础的ROW_NUMBER()讲透。它的逻辑最简单在分区内按指定顺序依次编号从1开始连续递增不存在并列。比如按分数降序排名两个人都是85分ROW_NUMBER()会给其中一个人编1号另一个人编2号谁前谁后由ORDER BY后面的辅助条件决定。SELECT StudentName, ClassName, Score, ROW_NUMBER() OVER ( PARTITION BY ClassName ORDER BY Score DESC, StudentID ASC ) AS RowNumRank FROM StudentScore WHERE SubjectName 数学;这个函数适合什么场景我举几个真实的业务例子行政班学号管理每个班学生需要一个唯一的序号不关心分数高低重叠只关心“排下来是第几个”。分页查询ROW_NUMBER()配合BETWEEN取某几行是SQL Server里做分页的经典做法。去重按某个维度分组后只保留每组第1条记录可以配合ROW_NUMBER()实现。它不处理并列这是设计如此不是缺陷。在我做过的很多报表里ROW_NUMBER()其实是使用频率最高的排名函数原因也很简单它生成的序号唯一、连续下游做透视表、画图表、做筛选都对这列序号特别友好。特别提醒一下ROW_NUMBER()的ORDER BY里如果只写了分数而同分的人又很多那么每次查询出来的顺序可能不一样。如果你希望“同分时按学号从小到大排”就必须在ORDER BY里把学号加上。这一点在实际工作中非常实用比如按成绩排名次的同时希望同分的学生按姓名笔画排序直接多写一个排序条件就行。2.2 RANK有并列就跳号的“奖学金排名”RANK()是为“允许并列但并列会占名额”的业务场景设计的。它的规则是遇到分数相同的人给相同的名次但下一个名次会跳过。比如两个人并列第1下一个就是第3没有第2名。在成绩排名场景里RANK()对应的是“奖学金、保送名额”这类逻辑假如一等奖学金只有1个名额出现两个并列第一那么名额其实是给出去2个第三名虽然成绩也很高但因为名额不够只能落到二等。用一个跳号的名次恰恰如实反映了这种“占了名额”的关系。SELECT StudentName, ClassName, Score, RANK() OVER ( PARTITION BY ClassName ORDER BY Score DESC ) AS RankResult FROM StudentScore WHERE SubjectName 数学;我做过一个学科竞赛的排名需求主办方要求必须用RANK()原因就是“并列的名次必须空出来后面的人名次按实际人数顺延”这样才能准确反映获奖档位的实际比例。如果你误用DENSE_RANK()会导致第三名算到第二档里去整个获奖名单对不上。2.3 DENSE_RANK不跳号的“等级排名”DENSE_RANK()的规则是并列名次相同但下一个名次不跳号。两个人并列第1下一个就是第2。它反映的是“按分数段划分等级”的思路。什么场景用DENSE_RANK()最常见的是等级测评。比如把学生成绩分成优、良、合格、不合格四个档次90分以上是优80-90是良。这时候用DENSE_RANK()得到的“第1档、第2档”就很契合等级概念跳不跳号反而不重要。还有体育比赛的“名次并列则共享奖牌”的规则也是DENSE_RANK()的语义。SELECT StudentName, ClassName, Score, DENSE_RANK() OVER ( PARTITION BY ClassName ORDER BY Score DESC ) AS DenseRankResult FROM StudentScore WHERE SubjectName 数学;2.4 三个函数的输出对比一张表说明白我把三个函数在相同数据下的输出放在一起对比这样大家一眼就能看出区别。假设某个班级数学成绩如下张三85、李四85、王五80、赵六78、钱七78、孙八70。学生分数ROW_NUMBERRANKDENSE_RANK张三85111李四85211王五80332赵六78443钱七78543孙八70664注意几处容易混淆的细节ROW_NUMBER()中的1、2并非“并列第一的意思”只是“排在前面的两条记录”谁先谁后取决于ORDER BY里的辅助字段。RANK()中85分占掉了名次1和2所以王五虽然只比并列第一低5分名次却直接掉到了3。DENSE_RANK()中两个85分都是第1王五就是第2中间没有空档。实际项目里这些语义直接决定了报表能不能对接业务规则。我一般建议开发者在写排名SQL之前先和业务确认清楚一个问题“并列的人下一个名次要不要跳号”。就这一句话就能确定该用RANK()还是DENSE_RANK()。3. 成绩排名实战从建表到完整查询3.1 建表与造数据为了让后面的案例能直接复现我先给出一张完整的建表语句和测试数据。表结构按实际业务设计不搞太复杂的范式够用就好。CREATE TABLE StudentScore ( StudentID INT NOT NULL, StudentName NVARCHAR(50) NOT NULL, ClassName NVARCHAR(20) NOT NULL, SubjectName NVARCHAR(20) NOT NULL, Score INT NULL, ExamDate DATE NULL );字段说明StudentID学生IDStudentName姓名ClassName班级SubjectName科目Score分数允许空值用来模拟缺考情况ExamDate考试日期。日期字段是为后面的进阶案例准备的。插入演示数据INSERT INTO StudentScore (StudentID, StudentName, ClassName, SubjectName, Score, ExamDate) VALUES (1, N张三, N一班, N数学, 85, 2024-11-01), (2, N李四, N一班, N数学, 85, 2024-11-01), (3, N王五, N一班, N数学, 80, 2024-11-01), (4, N赵六, N一班, N数学, 78, 2024-11-01), (5, N钱七, N一班, N数学, 78, 2024-11-01), (6, N孙八, N一班, N数学, 70, 2024-11-01), (7, N周九, N二班, N数学, 92, 2024-11-01), (8, N吴十, N二班, N数学, 88, 2024-11-01), (9, N郑十一, N二班, N数学, 75, 2024-11-01), (1, N张三, N一班, N语文, 78, 2024-11-01), (2, N李四, N一班, N语文, 90, 2024-11-01), (3, N王五, N一班, N语文, 85, 2024-11-01), (4, N赵六, N一班, N语文, 82, 2024-11-01), (5, N钱七, N一班, N语文, 65, 2024-11-01), (6, N孙八, N一班, N语文, 88, 2024-11-01), (7, N周九, N二班, N语文, 95, 2024-11-01), (8, N吴十, N二班, N语文, 92, 2024-11-01), (9, N郑十一, N二班, N语文, 78, 2024-11-01);这里有9个学生两个班两门课。为什么特意造两个班级的数据因为做分组排名测试数据必须跨组否则看不出PARTITION BY的作用。3.2 基础排名按班级对数学成绩排名需求查出所有学生的数学成绩并给出班级内的名次同时显示全班排名和全校排名。这次我直接把ROW_NUMBER、RANK、DENSE_RANK三个函数都放进来让大家看同一份数据的输出对比SELECT ClassName, StudentName, Score, ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS RowNumRank, RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS RankResult, DENSE_RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS DenseRankResult FROM StudentScore WHERE SubjectName 数学 ORDER BY ClassName, Score DESC;查询结果如下部分截取班级姓名分数RowNumRankRankResultDenseRankResult一班张三85111一班李四85211一班王五80332一班赵六78443一班钱七78543一班孙八70664二班周九92111二班吴十88222二班郑十一75333看到没这就是窗口函数和普通ORDER BY最大的区别名次是分组内重新从1开始的。一班的张三李四都是班级第1二班的周九也是班级第1。如果只用ORDER BY Score DESC你只能得到整体排序得不到“各班的第1名是谁”这个信息。3.3 多维度分组班级科目同时排名实际业务里很少只排数学一科通常要“每科都排名”。有人会想那我加WHERE SubjectName 数学查一次再改条件查一次当然可以但如果你希望一张表里同时看到所有学生的所有科目排名就得在PARTITION BY里同时指定多个字段。SELECT ClassName, SubjectName, StudentName, Score, ROW_NUMBER() OVER ( PARTITION BY ClassName, SubjectName ORDER BY Score DESC ) AS RankInClassAndSubject, RANK() OVER ( PARTITION BY ClassName, SubjectName ORDER BY Score DESC ) AS RankWithGap FROM StudentScore ORDER BY ClassName, SubjectName, Score DESC;这里的逻辑很有意思。PARTITION BY ClassName, SubjectName的含义是把班级和科目都相同的行分为一组然后在组内排名。效果就是“一班语文组内排名”、“二班数学组内排名”互不干扰。相比把全校所有人放在一起排序这种分组排名的粒度更细也更接近真实教务需求。如果把需求改成“只看全校单科排名不分班级”那就去掉PARTITION BY直接ORDER BY Score DESCSELECT ClassName, SubjectName, StudentName, Score, RANK() OVER (ORDER BY Score DESC) AS SchoolRank FROM StudentScore WHERE SubjectName 数学;注意这里OVER()里没写PARTITION BY表示整个结果集是一组排名就是全校排名。这也是容易忽略的细节。3.4 总分排名是怎么做的刚才举的例子都是单科排名。很多需求是按“总分”来排名。总分不是表里直接有的字段得先聚合。这里有个常见的坑不能在窗口函数里直接写SUM(Score)当成字段去排名得分步骤来。推荐的做法是先用GROUP BY把总分算出来再用窗口函数排名。两种写法都可行写法一用子查询SELECT StudentName, ClassName, TotalScore, RANK() OVER (PARTITION BY ClassName ORDER BY TotalScore DESC) AS ClassRank FROM ( SELECT StudentName, ClassName, SUM(Score) AS TotalScore FROM StudentScore GROUP BY StudentName, ClassName ) AS t ORDER BY ClassName, TotalScore DESC;写法二用CTEWITH StudentTotal AS ( SELECT StudentName, ClassName, SUM(Score) AS TotalScore FROM StudentScore GROUP BY StudentName, ClassName ) SELECT StudentName, ClassName, TotalScore, RANK() OVER (PARTITION BY ClassName ORDER BY TotalScore DESC) AS ClassRank FROM StudentTotal ORDER BY ClassName, TotalScore DESC;我个人更建议用CTE理由有两个一是可读性好逻辑步骤清晰二是后续如果要继续做过滤比如只要各班前3名CTE可以直接往下接比层层嵌套的子查询好维护得多。4. 进阶场景TopN、并列策略与成绩波动分析4.1 查每班前三名经典TopN问题“每个班级取总分前三名”几乎是成绩排名需求里出现频率最高的也是窗口函数最能体现优势的场景。这里用ROW_NUMBER()实现。WITH StudentTotal AS ( SELECT StudentName, ClassName, SUM(Score) AS TotalScore FROM StudentScore GROUP BY StudentName, ClassName ), RankedStudents AS ( SELECT StudentName, ClassName, TotalScore, ROW_NUMBER() OVER ( PARTITION BY ClassName ORDER BY TotalScore DESC, StudentName ASC ) AS Seq FROM StudentTotal ) SELECT ClassName, StudentName, TotalScore, Seq FROM RankedStudents WHERE Seq 3 ORDER BY ClassName, Seq;为什么这里用ROW_NUMBER()而不是RANK()因为业务上通常说的“每班前三名”隐含了一个前提名额只有3个。如果第3名和第4名同分ROW_NUMBER()会硬排出一个3和一个4最终选出3个人而RANK()会把并列的都算进去可能选出4个人。到底哪个对取决于需求。我一般会先问一句“同分要并列吗”然后再决定用哪个函数。在不确定的情况下代码里注释写清楚“此处使用ROW_NUMBER同分不并列如需并列请改用RANK/DENSE_RANK”给后来接手的人省很多沟通成本。4.2 同分保留并列名次的做法如果业务要求“同分必须同名次”那么TopN查询就需要换策略。比如“每班排名前20%的学生进入复试”此时并列必须保留再用ROW_NUMBER()就不合适了。一种处理方法是先用DENSE_RANK()算出并列名次再以名次作为筛选条件。比如取每班前3个名次WITH StudentTotal AS ( SELECT StudentName, ClassName, SUM(Score) AS TotalScore FROM StudentScore GROUP BY StudentName, ClassName ), RankedStudents AS ( SELECT StudentName, ClassName, TotalScore, DENSE_RANK() OVER ( PARTITION BY ClassName ORDER BY TotalScore DESC ) AS DenseRank FROM StudentTotal ) SELECT ClassName, StudentName, TotalScore, DenseRank FROM RankedStudents WHERE DenseRank 3 ORDER BY ClassName, DenseRank;注意这个写法会输出“前3个名次”的所有人即使某个名次有5个人并列也会全部输出。这在实际业务中常常能给人惊喜你以为只录取9个人结果因为并列实际录取了12个人。遇到这种情况不能怪SQL只能怪业务当初没把并列规则说清楚。作为开发者的职责是把规则的差异提前暴露出来让业务方做决策。4.3 一次查询同时出多个维度的排名实际报表中经常要“既看班级排名又看全校排名”。这在以前是两套SQL拼表用窗口函数可以在一条SQL里同时完成。SELECT ClassName, StudentName, SubjectName, Score, RANK() OVER ( PARTITION BY ClassName, SubjectName ORDER BY Score DESC ) AS ClassSubjectRank, RANK() OVER ( PARTITION BY SubjectName ORDER BY Score DESC ) AS SchoolSubjectRank FROM StudentScore WHERE SubjectName 数学 ORDER BY ClassName, Score DESC;查询结果班级姓名科目分数ClassSubjectRankSchoolSubjectRank一班张三数学8512一班李四数学8512一班王五数学8034二班周九数学9211二班吴十数学8823这个查询很有意思同一个RANK()函数PARTITION BY的粒度不同算出来的名次含义就完全不同。张三在班内并列第1放到全校数学排名就只有并列第2了因为二班周九的92分更高。一眼看去班级排名和学校排名的差异就暴露出来了。这种“同屏对比”对老师分析班级教学质量特别有用。4.4 窗口聚合与成绩趋势分析排名之外还有一种常见需求算班级平均分、最高分、每个学生和平均分的差距。这些聚合值也可以用窗口函数直接引用。SELECT ClassName, StudentName, SubjectName, Score, AVG(Score) OVER (PARTITION BY ClassName, SubjectName) AS ClassAvgScore, MAX(Score) OVER (PARTITION BY ClassName, SubjectName) AS ClassMaxScore, Score - AVG(Score) OVER (PARTITION BY ClassName, SubjectName) AS DiffFromAvg FROM StudentScore WHERE SubjectName 数学 ORDER BY ClassName, Score DESC;这里AVG(Score) OVER(...)对每一行都返回“所属分组的平均分”不需要再额外写一个子查询去关联。这个特性在做成绩分析报表时非常实用你可以直接在明细表旁边加一列“和班级平均分的差距”不用先聚合再关联一条SQL就搞定。如果要分析“一个学生连续多次考试的成绩波动”可以引入LAG()滞后函数配合窗口排序。比如查每个学生在历次数学考试中的分数变化SELECT StudentName, ExamDate, Score, LAG(Score, 1) OVER ( PARTITION BY StudentName, SubjectName ORDER BY ExamDate ) AS PreviousScore, Score - LAG(Score, 1) OVER ( PARTITION BY StudentName, SubjectName ORDER BY ExamDate ) AS ScoreChange FROM StudentScore WHERE SubjectName 数学 ORDER BY StudentName, ExamDate;LAG(Score, 1)的意思是取当前行的前一行按ORDER BY ExamDate排序后的分数。这样就能很直观地看到某学生上次考了多少、这次考了多少、涨了还是跌了。配合前面的排名函数你甚至可以算“名次变化”即上次和这次在班内的排名差用于追踪学生的学习状态。这些组合用法是传统GROUP BY很难实现的。5. 常见问题与排查技巧实录5.1 忘了PARTITION BY还是忘了ORDER BY窗口函数有个硬性要求排名函数必须有ORDER BY否则直接报错。SQL Server会提示“窗口函数不允许没有ORDER BY”。这个报错其实是在帮我们排名这个概念本身就是建立在一个明确的排序规则之上的没有顺序哪来的名次常见的错误写法-- 错误没有ORDER BY ROW_NUMBER() OVER (PARTITION BY ClassName) -- 正确 ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY Score DESC)我见过不少同事在初学阶段反复栽在这个细节上。解决的办法就是把窗口函数的三段式写全OVER括号里先问自己三个问题——按什么分组按什么排序要不要限定行范围分组对应PARTITION BY排序对应ORDER BY范围对应ROWS BETWEEN。三个问题都回答了语法就不会错。5.2 排名结果和预期不同大概率是ORDER BY方向错了排名默认是升序排序也就是数值越小名次越靠前。成绩排名当然要分数越高名次越靠前所以必须写ORDER BY Score DESC。写反成ORDER BY Score ASC结果就是“分数最低的人排第1”导出报表的时候才发现问题那场面是真的尴尬。-- 反例分数最低的排第1 RANK() OVER (PARTITION BY ClassName ORDER BY Score ASC) -- 正例分数最高的排第1 RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC)这里我分享一个检查技巧写完排名SQL后先别急着看全部结果取一个分组的前两条数据人工比对一下。比如查一班数学排名看一下第1名的分数是不是班里最高的。用常识去验证SQL结果比盯着代码逻辑检查快得多。5.3 为了排名去用GROUP BY把明细搞丢了很多新手会混淆GROUP BY和PARTITION BY最典型的现象是用了GROUP BY之后查询结果的行数变少了原本每个学生的明细都看不见了。这是因为GROUP BY把多行折叠成一行汇总失去明细是它的设计特点。-- 错误示范想排名结果连明细都没了 SELECT ClassName, StudentName, Score, RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC) FROM StudentScore GROUP BY ClassName, StudentName, Score;这段SQL能跑但结果集被聚合成“每个学生每个分数一行”如果同一个学生有多条记录比如多次考试聚合会把数据压扁排名自然就不对了。正确做法是需要明细就用PARTITION BY需要汇总就用GROUP BY两者可以直接结合使用——先用GROUP BY算汇总值再用窗口函数对汇总值排名前面总分示例里就是这么处理的。5.4 WHERE里直接用排名列报“列名无效”一个特别常见的错误想筛选“班级排名前3的学生”直接在WHERE里写排名函数。-- 错误WHERE里不能直接用窗口函数结果 SELECT ClassName, StudentName, Score, RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS RankResult FROM StudentScore WHERE RankResult 3;这样写会报错因为窗口函数在WHERE之后才计算WHERE执行时根本看不到RankResult这列。这个限制不只在SQL Server在所有主流数据库里都一样因为这是SQL标准的执行顺序决定的。正确做法是先把排名算出来作为子查询或CTE然后在外面包一层WHERE过滤WITH Ranked AS ( SELECT ClassName, StudentName, Score, RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS RankResult FROM StudentScore WHERE SubjectName 数学 ) SELECT * FROM Ranked WHERE RankResult 3;道理很简单窗口函数的结果是“计算完才有的新列”要在这个结果上过滤就必须让它先存在所以需要一层包裹。养成“写窗口函数就想到包一层CTE”的习惯这类错误能减少九成。5.5 同分排名到底算并列还是不算并列这个问题在业务层面非常关键但很多人在写SQL之前没跟业务确认。我在前面反复强调这点是因为教训太深刻了。一次我带团队做一份“星级教师评定”规则是“按学生满意度评分排名前10名评为五星”。结果因为用了ROW_NUMBER()而不是DENSE_RANK()第10名处恰好有两个同分的人系统硬把其中一个人排到第11名直接落选。事后复盘业务方说“同分当然应该并列”白纸黑字的规则里却没写清这一条。所以我的建议是写排名SQL之前先问需求方三句话同分的人名次要并列吗并列之后后面的名次要跳号吗如果名额有限比如前10名并列导致超过10个人怎么办这三个问题问完用哪个函数基本就定了不要并列用ROW_NUMBER()要并列且跳号用RANK()要并列且不跳号用DENSE_RANK()。把这三句话问明白能省掉后面大量的返工和扯皮。5.6 数据量大时排名查询慢怎么优化成绩表数据量很容易做大。一所学校几千个学生每次考试几十万条记录排名SQL如果写得不好报表跑几分钟出不来很正常。我总结了几条实用的优化经验为分区和排序字段建立索引。PARTITION BY和ORDER BY用到的字段比如ClassName、SubjectName、ExamDate、Score组合索引对窗口函数性能提升非常明显。SQL Server的窗口函数排序往往是内存中的排序操作如果数据能通过索引预排序开销会小很多。提前用WHERE过滤数据。窗口函数是在WHERE过滤之后才执行的所以能提前缩小数据集就一定先过滤。比如“只看本学期数据”先WHERE ExamDate 2024-09-01再排名数据量能少一大截。避免在窗口函数ORDER BY里用表达式。ORDER BY YEAR(ExamDate)这种写法会阻止索引利用每次都要算一遍表达式再排序。如果确实需要按年排序提前算好一列ExamYear存到表里会更快。用临时表分步处理。当SQL特别复杂、嵌套特别深时我倾向于先跑一个中间结果到临时表或表变量再对中间结果做排名。虽然多一步但每步的逻辑简单清晰执行计划也更稳定排查问题也容易得多。5.7 常见问题速查表我整理了一张速查表把成绩排名场景里最容易踩的坑都列出来。问题表现解决方案忘记写ORDER BY报错“窗口函数需要ORDER BY”排名函数必须写ORDER BY指定排序规则ORDER BY方向写反最低分排第1成绩排名用ORDER BY Score DESC在WHERE中用排名列报错“列名无效”包一层CTE或子查询再过滤GROUP BY折叠明细行数变少排名错乱明细排名用PARTITION BY不要用GROUP BY同分处理不符合预期名次出现断档或不断档先确认业务规则再选RANK/DENSE_RANK数据量大查询慢报表跑几分钟建索引、先过滤、避免ORDER BY表达式5.8 一个小众但有用的排查技巧最后分享一个排查技巧属于我自己的独门经验。当排名结果看起来不对又说不清哪里错的时候我会把窗口函数的PARTITION BY字段拼到SELECT里一起输出。比如排查“一班数学排名不对”时把ClassName、SubjectName、Score都列出来对照着肉眼检查。很多时候问题出在数据本身Score里有NULL值排序时NULL会被放在最前面还是最后面取决于数据库设置SQL Server里ASC排序NULL在前DESC排序NULL在最后这在成绩排名里就导致“缺考的人排到了最后”如果业务上希望缺考不计入排名那要注意提前过滤-- 排除缺考Score IS NULL后排名 SELECT ClassName, StudentName, Score, RANK() OVER (PARTITION BY ClassName ORDER BY Score DESC) AS RankResult FROM StudentScore WHERE SubjectName 数学 AND Score IS NOT NULL ORDER BY ClassName, RankResult;还有一种情况是StudentName存在同名不同人的问题一旦按姓名分组排名两个人的成绩会被揉到一起。这种问题靠看SQL代码是看不出来的只有把分组字段列出来肉眼验数据才能发现。所以我的习惯是任何窗口函数排完名先输出一遍分组字段排序列人工抽查几行再交到业务手里。排查问题的效率往往就体现在这种小习惯上。写在最后的一点经验成绩排名这个需求看起来简单但真做起来涉及的细节非常多。我用partition by函数做过很多次成绩报表之后最大的感受是窗口函数真正改变了写SQL的思维方式。以前遇到排名需求第一反应是“要不要建临时表”“子查询怎么嵌套”现在第一反应是“这个排名的分组粒度是什么、并列规则是什么”想清楚这两点SQL写起来就顺了。另外有人说RANK()用得最多但我实际项目里反而觉得ROW_NUMBER()的使用频率更高因为业务上更多是“取固定数量的人”——前三名、前10名、前20%这时候ROW_NUMBER()直接配合WHERE Seq N就完了。真正用RANK()和DENSE_RANK()的场景往往伴随着复杂的并列规则和名额分配需要跟业务反复确认。写代码前多问一句“同分怎么算”省下来的精力够你多写好几条SQL的。如果你手里的业务场景也涉及成绩排名建议先拿本文的示例数据把三个排名函数都跑一遍感受一下输出差异再套到自己真实的数据表上。熟练之后你会发现partition by能做的远不止排名它和聚合函数、滞后函数配合起来几乎能覆盖成绩分析里九成以上的需求。