1. 题目核心成绩表里排座次难点不在SQL而在“并列怎么办”1.1 题目给了什么一个极简的分数表这道题我印象很深LeetCode编号178标题就叫“分数排名”。题面极简一张Scores表只有两个字段Id自增主键和Score分数。要求对分数做排名规则是按分数从高到低排如果两个分数相同它们名次相同且下一名的名次要连续不能出现跳号。举个例子原始数据是这样的IdScore13.5023.6534.0043.8554.0063.65期望输出的结果是ScoreRank4.0014.0013.8523.6533.6533.504注意看细节两个4.00并列第一接下来3.85直接排第二而不是第三。两个3.65并列第三再往下3.50排第四。有没有发现这里用的是“不跳号的并列排名”。很多新手第一次做这道题第一反应是“这不就ORDER BY Score DESC一下就行了吗”——能排出来但拿不到名次那一列。排序是排序排名是排名两者之间差了“如何计算位次”这一步。而并列名次要不要跳号恰恰是面试官最想确认你懂不懂的细节。1.2 三个容易被忽略但决定成败的细节我拿这道题给别人讲的时候通常会强调三个隐藏考点第一名次从1开始不是从0开始。这听起来像废话但写COUNT(DISTINCT ...)或ROW_NUMBER()的时候很多人会忘记处理起始值导致第一名显示0。第二并列必须同名次。两个4.00都是Rank 1不能一个1一个2。这意味着不能用ROW_NUMBER()直接硬排因为它给每一行分配绝对唯一的序号并列也会被拆开。这个函数在这道题里反而是错的。第三并列之后名次不跳号。也就是3.85是第2名不是第3名。这决定了你要用DENSE_RANK()而不是RANK()。这两个函数长得像行为差异非常关键后面我会专门展开。这三个细节如果没想清楚后面无论用什么解法都会写错。我见过太多人卡在“为什么用RANK不对”“为什么COUNT出来少1”这种问题上根源就是对这三条规则没有先做拆解。1.3 为什么这道题成了“必刷经典”178题能在刷题网站上有这么高的地位不单是因为题目简单、适合入门。它背后站着一整类真实需求考试排名、游戏天梯、销售业绩排行、积分榜、商品好评榜……凡是要把“一堆数字”变成“一个有意义的顺序标签”的场景都会遇到这三个细节。而且这道题的解法不止一种。窗口函数能做自连接能做相关子查询能做MySQL 5.7时代的变量也能做。同一个业务语义在不同技术背景下有完全不同的实现路径这本身就很有价值。我自己面试候选人的时候也喜欢拿这道题当“试金石”先问会不会窗口函数再问知不知道三种排序函数的区别最后追问一句“如果你用的数据库版本不支持窗口函数怎么办”。这一套下来SQL水平基本能摸个大概。所以这篇文章不打算只给一个标准答案。我想把这道题从“背解法”上升到“理解排名逻辑”从窗口函数到自连接再到相关子查询逐个拆给你看。2. 排序逻辑拆解RANK、DENSE_RANK、ROW_NUMBER三者到底差在哪2.1 同一组数据三种函数给出三种结果窗口函数是这道题最主流的解法SQL Server、PostgreSQL、Oracle、MySQL 8.0及以上都支持。但窗口函数里能“产生排名”的至少有三个ROW_NUMBER()、RANK()、DENSE_RANK()。新手最容易在它们之间犹豫。还是上面那张表三个函数分别跑一遍结果差异非常清楚ScoreROW_NUMBERRANKDENSE_RANK4.001114.002113.853323.654433.655433.50664ROW_NUMBER()不管分数是否相同强制给每行一个连续且唯一的序号1、2、3、4、5、6。你完全没法从序号看出“4.00和4.00其实是并列的”。RANK()识别了并列两个4.00都是1但下一个直接跳到3中间空出2。它遵循的是体育比赛里常见的“赛事排名”逻辑两个人并列第一那下一名就是第三名。DENSE_RANK()也识别并列但并列之后不跳号两个1下一个是2再下一个是3。这正是178题要求的语义。一个特别直观的记忆方式RANK会产生“稀疏排名”排名数字中间可能有空洞DENSE_RANK是“稠密排名”排名数字连续不断。DENSE这个词本身就有“密集、稠密”的含义可以帮你记一辈子。2.2 真实业务里怎么选考试榜、积分榜、TopN各不相同三个函数没有绝对好坏只有业务语义不同。我举几个实际例子你就明白了。考试总分排名通常用DENSE_RANK。两个学生都考了700分那他们都该是并列第一下一名是第二。如果用了RANK第二名就凭空消失了家长看到成绩单会觉得“第三名上面是谁为什么第二名没人”很难解释。游戏天梯榜和竞赛排名通常用RANK。竞技类积分讲究“名次差”带来的压迫感。两个人并列第一第三名的实际位置就是第三名跳过一个位置反而符合玩家心理预期前面有两个大佬我排第三。这种场景下RANK的跳号恰恰是对的。给每一行生成一个唯一行号用ROW_NUMBER。比如要给订单按时间从新到旧编号每一行必须是唯一的1、2、3、4不能出现两个1因为后续要按行号做分页、抽样或精确更新。这时候哪怕金额相同、时间相同也必须用ROW_NUMBER强行分出先后。所以178题为什么选DENSE_RANK而不是RANK因为题目明确说了“rank should be consecutive”——排名必须是连续的。这类题面措辞就是业务需求文档读题时多抠一下字眼能省很多试错时间。2.3 记住一句话比背函数签名强我在带人的时候会让他们记住这句话ROW_NUMBER管唯一序号RANK管并列且跳号DENSE_RANK管并列不跳号。这句话不需要死记硬背函数说明而是让你在写SQL前先问一句业务上并列存在吗并列后名次要连续吗并列不存在 →ROW_NUMBER并列存在且允许跳号 →RANK并列存在且不跳号 →DENSE_RANK178题是第三种。确认好这一点题已经解了一半。剩下的只是把DENSE_RANK()放进窗口函数里再ORDER BY一下而已。3. 第一种解法DENSE_RANK窗口函数也是标准答案3.1 整条SQL长什么样直接上代码这应该是LeetCode 178题最经典的解法SELECT Score, DENSE_RANK() OVER (ORDER BY Score DESC) AS Rank FROM Scores;你没看错就三行。这也是这道题被很多人称为“送分题”的原因——前提是你知道DENSE_RANK这个函数。在MySQL 8.0、PostgreSQL、SQL Server、Oracle里这条SQL都能直接跑。如果面试官没有额外限制写这个解法是最稳妥的。3.2 逐段解释为什么这么写就对了我把这条SQL拆成三部分看。SELECT Score没什么好说的取原始分数。DENSE_RANK() OVER (...)是核心。OVER关键字声明这是一个窗口函数括号里没有PARTITION BY说明不分组整个结果集当作一个窗口。ORDER BY Score DESC定义窗口内排序规则分高的排前面。DENSE_RANK()根据这个排序结果计算名次遇到相同分数给相同名次且不跳号。Rank是别名。这里我特意用双引号括起来是因为RANK在某些数据库里是保留关键字不加引号可能报错。LeetCode的MySQL执行环境通常能直接写AS Rank但我建议你养成加引号的习惯“Rank”。避免了关键字冲突也显得专业。3.3 面试爱追问的执行顺序问题能写出上面这条SQL只能算“会背”。面试官通常会追问一句窗口函数的计算发生在SQL执行的哪一步这个问题的标准答案是窗口函数在GROUP BY、HAVING之后ORDER BY之前计算。如果把一条SQL的执行顺序粗略列出来大概是FROM确定数据源WHERE过滤原始行GROUP BY分组HAVING过滤分组窗口函数在这一步之后开始计算SELECT输出列窗口函数的结果在这里参与输出ORDER BY排序LIMIT截断所以在这道题里先确定要处理的是Scores整张表没有过滤、没有分组直接进入窗口函数计算给每一行算出名次最后按Score DESC排序输出。理解了执行顺序就能解释一个常见疑问“为什么WHERE里不能直接用Rank 1来过滤”因为WHERE发生在窗口函数之前此时Rank根本还不存在。想要“每个分组排名第一”这种结果必须得先算出字段再在外面套一层子查询这也是后面实战变形里必备的思路。3.4 变体如果题目要求变了怎么改面试官有时候会把题改一改但只要你会了DENSE_RANK其他变体也只是换函数而已改成允许跳号的并列排名把DENSE_RANK()换成RANK()一行改动。改成唯一连续序号不并列换成ROW_NUMBER()一行改动。要求按分数从低到高排名把ORDER BY Score DESC改成ORDER BY Score ASC。要求每个班级单独排名多加一个PARTITION BY ClassId即可。我把这些变体列成一个表方便你对照业务需求窗口函数写法并列同名次名次连续DENSE_RANK() OVER (ORDER BY Score DESC)并列同名次名次跳号RANK() OVER (ORDER BY Score DESC)每行唯一序号不分并列ROW_NUMBER() OVER (ORDER BY Score DESC)按班级分组后再排名DENSE_RANK() OVER (PARTITION BY ClassId ORDER BY Score DESC)你看这道题本质上是“一个函数记不记得住、一条规则能不能看懂”的事。但很多刷题的人恰恰卡在规则的文字表述上把“consecutive”忽略了导致写了个RANK还一脸疑惑地自言自语“为什么输出不对”。4. 第二种解法自连接 COUNT(DISTINCT)没有窗口函数也能做4.1 核心思路数一数有多少个比自己高或相等的去重分数窗口函数虽然一行搞定但它对数据库版本有要求。MySQL 5.7及更早版本不支持窗口函数很多老系统到现在还在用5.7。更重要的是面试官为了考察你对SQL本质的理解会故意限制你“不要用窗口函数你怎么做”这时候自连接就派上用场了。核心思路非常朴素一个人的名次等于“分数高于自己的人数 1”。如果有3个人的分数比他高他就是第4名如果有0个人比他高他就是第1名。但这里有个“并列怎么办”的坑。假设有两个人都是4.00分要计算一个3.85分的人的名次。比3.85高的分数是4.00可是有两条4.00的记录。如果直接“数行数”会数到2于是名次变成3但期望结果是2因为两个4.00是同一个名次只算“一个更高的分数”就够了。所以关键不是“有多少行分数高于自己”而是“有多少个不同的分数高于自己”。必须加DISTINCT去重。这就是COUNT(DISTINCT s2.Score)为什么是这条SQL的灵魂。4.2 两种常见的自连接写法第一种写法自连接之后按每条原始记录分组统计比自己分数高的“不同分数个数”再加1。SELECT s1.Score, (SELECT COUNT(DISTINCT s2.Score) FROM Scores s2 WHERE s2.Score s1.Score) 1 AS Rank FROM Scores s1 ORDER BY s1.Score DESC;这里用了相关子查询严格来说不完全是“自连接”但对每一行Scores s1都去子查询里找“分数高于自己”的去重分数数量。比如4.00分那条子查询里没有分数比它高计数为0加1得13.85那条子查询里只有4.00一个不同分数计数为1加1得23.65那条子查询里有4.00和3.85两个不同分数计数为2加1得3。第二种写法更“正宗”的自连接把两张表做笛卡尔连接再用连接条件过滤。SELECT s1.Score, COUNT(DISTINCT s2.Score) AS Rank FROM Scores s1 INNER JOIN Scores s2 ON s2.Score s1.Score GROUP BY s1.Id, s1.Score ORDER BY s1.Score DESC;重点在于s2.Score s1.Score这个连接条件。它是“把每一个分数和所有不低于它的分数配对”。以最高分4.00为例它只能和自己配对因为不存在比它更高的分数DISTINCT s2.Score只有4.00一个值计数1名次正好是1。对3.85来说它能和4.00配对也能和自己配对去重后有两个值名次2。对3.65来说能配上4.00、3.85和自己去重后三个值名次3。一路下来每个分数直接数“有多少个不同的分数不低于自己”正好就是自己的名次。这种写法为什么不用加1因为连接条件用了把“自己”也算进去了。自己永远占一个不同的分数位所以计数直接从1开始。4.3 为什么需要DISTINCT不去重的后果我把这个坑单独拿出来说是因为它真的很隐蔽。还是用4.00分举例假设有两条4.00的记录要算一个3.50分的人的名次。如果不加DISTINCTCOUNT(s2.Score)会同时数到两个4.00再加上3.85、3.65、3.50自己一共5行名次5。但期望名次是4因为4.00虽然出现两次只是同一个名次。加了DISTINCT之后4.00只算一个不同的值计数就是4。这个误区我见过不少尤其是从ROW_NUMBER思维转过来的人容易下意识认为“排名就是数多少行比自己靠前”。数据没有重复时这个逻辑没错一旦出现并列立刻翻车。所以记住这句话去重的是“分数值”不是“记录行”。4.4 与窗口函数做复杂度对比从可读性和性能上看窗口函数都是更优选它只需要扫描一遍数据排序完直接出结果而自连接相当于两张表做连接数据量大时开销明显上升。在LeetCode那几张玩具表上可能感受不到差距但你把它放到几百万行的真实订单表上跑一次执行计划能差出好几倍。不过自连接解法展现了SQL最底层的集合思维“结果”不过是通过特定的连接条件把表和自己组合成一张新表再做聚合。这种思维方式在业务中遇到奇奇怪怪的“没有现成函数可用”的老数据库时是能救命的能力。5. 第三种解法相关子查询理解执行顺序的绝佳素材5.1 子查询写法长什么样前面提到的第一种自连接写法其实就是相关子查询。我再把它单独拎出来是因为那条SQL太适合用来理解“SQL是如何一行一行处理数据的”。SELECT s1.Score, (SELECT COUNT(DISTINCT s2.Score) FROM Scores s2 WHERE s2.Score s1.Score) AS Rank FROM Scores s1 ORDER BY s1.Score DESC;和上一节的自连接写法对比这版在SELECT子句里嵌入了一个子查询。子查询返回的是“当前这一行条件下”的一个标量值一个数字这个数字就是名次。以第一行4.00为例主查询取到s1.Score 4.00子查询执行SELECT COUNT(DISTINCT s2.Score) FROM Scores s2 WHERE s2.Score 4.00结果只有4.00一个值计数1所以Rank 1下一行可能又是4.00子查询照样返回1。再往下一行是3.85子查询执行WHERE s2.Score 3.854.00和3.85都被计入计数2。每一行都重新执行一次子查询相当于拿着主查询当前行的分数去全表问一遍“有几个不同的分数达到这个线”。5.2 MySQL里相关子查询是怎么跑的很多教材说“相关子查询的性能不好”但很少解释为什么。实际上MySQL对相关子查询的处理很大程度上是针对每一行都执行一次子查询。主表有1000行子查询就可能被触发1000次每次都扫描完整张表做过滤和聚合。这样一来总体的扫描量近似是“行数 x 表大小”数据量一大就很吃亏。这也是为什么现在生产环境里能用窗口函数就用窗口函数。窗口函数只需要一次扫描、一次排序就能完成同样的计算性能差距可能会是数量级的。但如果你的面试官就是要考你手写相关子查询那重点考察的其实是你懂不懂“主查询每一行会代入子查询重新算”这件事。MySQL 8.0在某些场景下会把相关子查询改写成LEFT JOIN或半连接来优化但是依赖优化器这种变量远不如自己把SQL写干净来得踏实。5.3 三个坑性能、空值、别名用相关子查询解178题有三个方面要额外小心。性能问题最直观。LeetCode的样例表就几行跑起来毫无感觉。但如果你把同样的逻辑搬到一张100万行的订单表可能几十秒都出不来。我自己在业务上写报表时吃过这个亏一个简单的排名需求用子查询跑了快一分钟改成窗口函数后一百毫秒不到。那次之后我形成了一条习惯——能用窗口函数绝不用相关子查询。空值问题容易被忽略。如果Score列允许为NULLCOUNT(DISTINCT s2.Score)会忽略NULL值但WHERE s2.Score s1.Score遇到NULL比较时结果是不确定的NULL 某个值结果是NULL也就是不满足条件这可能导致排名结果和业务预期不一致。真实场景里缺考的0分和缺考的NULL完全是两码事建议先WHERE Score IS NOT NULL过滤或在计算前用COALESCE(Score, 0)把NULL归一化。别名问题。我前面特意用了双引号Rank这是因为RANK是很多数据库的保留字。在外层SQL里如果你写AS Rank不带引号MySQL可能照样报错。刷题平台可能对这类问题比较宽容但到了真实项目里你不想在Code Review时被DBA盯上。6. 从题目到实战真实报表里的排名需求和变形6.1 按组排名PARTITION BY的加入178题只要求全局排名但真实业务里最常见的需求其实是“分组排名”。比如按班级给每个人排名按部门给员工销售额排名按区域给门店销量排名。这时候只需要给窗口函数加一个PARTITION BYSELECT DepartmentId, EmployeeName, Salary, DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS Rank FROM Employees;PARTITION BY的作用是把数据在窗口函数内部切成多个独立的“小窗口”每个窗口各自算排名。可以通俗理解成先按部门把员工分到几个小组里再在每个小组内按工资从高到低排座次。这个写法也是处理“每个组TopN”问题的基础。比如我要“每个部门工资最高的前三名”不能直接在WHERE里写Rank 3因为窗口函数在WHERE之后才计算。标准做法是套一层子查询SELECT DepartmentId, EmployeeName, Salary, Rank FROM ( SELECT DepartmentId, EmployeeName, Salary, DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS Rank FROM Employees ) t WHERE t.Rank 3;这层嵌套几乎是必考题。会写178题只是掌握了窗口函数的基础用法会配合PARTITION BY和子查询做TopN才算把它用进真实报表里。6.2 分数列存在NULL时怎么处理排名需求里NULL是个老问题。假设一张成绩表缺考的同学Score是NULL而不是0直接ORDER BY Score DESC时不同数据库对NULL的排序规则不完全一样MySQL默认把NULL排到最后Oracle默认NULL排在最前PostgreSQL默认NULL排在最后但可以改写NULLS FIRST。这就会导致一个尴尬同一个排名需求在不同数据库上跑出来的名次不同。更麻烦的是自连接和子查询里的比较运算。NULL 某个数的结果不是TRUE也不是FALSE而是UNKNOWN相当于这条记录被连接条件排除掉。可能你原本想统计“所有分数高于自己的人”但NULL分数的人既不被算作“高于别人”也不被别人算作“高于自己”名次直接被扭曲。我的建议是建表时如果分数允许为空就用默认值兜底比如DEFAULT 0实在要用NULL表示“缺考”那计算排名前先过滤掉缺考行或者COALESCE(Score, 0)把它转成0再排。排名表里出现NULL往往是统计口径混乱的信号。6.3 更多的排名派生函数PERCENT_RANK和CUME_DIST如果业务不只是要“第几名”还要看“排在前百分之多少”可以使用另外两个窗口函数PERCENT_RANK()和CUME_DIST()。PERCENT_RANK()返回的是相对排名计算公式是(RANK() - 1) / (总行数 - 1)。CUME_DIST()返回的是累积分布表示“有多少比例的行小于等于当前行”。举个例子还是178题那张表ScoreDENSE_RANKPERCENT_RANKCUME_DIST4.00100.3333334.00100.3333333.8520.40.53.6530.60.8333333.6530.60.8333333.50411这两个函数在绩效评级、成绩等级划分A/B/C/D、价格区间分析里特别实用。如果你理解了178题的排名语义再上手这两个函数会非常顺——它们本质上就是在排名基础上再算一次比例。7. 我在跑题目和写业务SQL时踩过的几个坑7.1 写窗口函数前先确认数据库版本我第一次在项目里用DENSE_RANK兴致勃勃地写好SQL结果在测试库上一执行直接报语法错误。查了半天才发现那套系统用的还是MySQL 5.6窗口函数是8.0才引入的。这个坑特别容易踩因为LeetCode和本地开发环境一般都比较新窗口函数随便用但生产环境的数据库版本可能落后好几年。遇到版本不支持的情况有两条路一是用变量模拟窗口函数MySQL 5.7时代最经典的写法是这样SET prev : -1, rank : 0; SELECT Score, rank : IF(prev Score, rank, rank 1) AS Rank, prev : Score FROM Scores ORDER BY Score DESC;思路是定义两个用户变量一个存上一行的分数一个存当前名次。如果当前行分数等于上一行分数名次不变否则名次加1。注意prev : Score这条赋值语句的顺序如果在rank计算之前就把prev更新了IF比较的就是当前行自己永远相等排名全部变成1。二是在SQL里通过自连接和相关子查询硬扛就是前面的解法二和解法三。虽然性能差一点但在老版本里属于“有且仅有”的正统思路。这两条路都掌握你才不会在面试或工作中被版本问题卡住。7.2 排名结果一样不代表排名逻辑正确我见过不少人写ROW_NUMBER() OVER (ORDER BY Score DESC)跑178题一看输出还“挺像那么回事”4.00排1、4.00排2、3.85排3。这是最危险的“看似正确”。为什么说危险因为题目要求两个4.00并列第一你给它们排成了1和2语义完全不同。如果把这套逻辑放到竞赛榜单里等于说选手A和选手B都是最高分但一个冠军一个亚军没人能接受。所以我做这类题有个习惯拿到输出后不只盯着前几行看专门去找“并列的数据”确认它们的排名是否一致。没有重复数据的测试用例会把错误的排名函数伪装成正确答案。7.3 排序不稳定导致的“榜单跳动”这个问题在刷题时完全看不出来但放到真实业务里非常坑。假设一个积分榜单按积分从高到低排名。积分相同的用户有很多ORDER BY Score DESC只按分数排序没有指定第二排序键。数据库底层是并行扫描的两次查询返回的相同积分行顺序可能不一样。你在第一页看到的用户刷新一下可能和第二页的用户换了位置——明明积分没变排名却“跳了”。解决办法很简单给ORDER BY加一个唯一键作为次级排序比如ORDER BY Score DESC, Id ASC。这样即使分数相同行顺序也是确定的翻页才不会乱。这个经验是我在跑一个活动榜单接口时发现的。当时用户疯狂投诉“排名乱跳”排查到最后不是排名算法错了而是排序不稳定加上分页逻辑导致的。要是早点想到加唯一键做次级排序能少加好几天班。7.4 从“查得对”到“查得快”索引怎么加如果一张表很大排名查询会非常吃性能。窗口函数内部要做排序自连接更是要反复扫描表。这时候索引的作用就很关键了。针对178题这种“按分数排序并排名”的需求给Score字段建索引能显著加速CREATE INDEX idx_scores_score ON Scores(Score);窗口函数执行ORDER BY Score DESC时可以直接利用索引的有序性减少排序开销。自连接里的ON s2.Score s1.Score也能通过索引快速定位符合条件的记录。但要注意索引不是万能的。如果你的查询还要PARTITION BY分组那可能更适合建组合索引比如(DepartmentId, Salary)。一般情况下先跑一下EXPLAIN看看执行计划确认有没有用到索引再决定怎么调整。7.5 排名需求的三段式自查清单做了这么多道排名题、写了这么多条排名SQL之后我总结出一个三段式自查清单每次写完都照着过一遍能挡掉绝大多数低级错误并列存在吗如果数据可能有重复分数确认自己选的是DENSE_RANK还是RANK是不是业务要的那种跳号语义。并列之后名次要连续吗题目里说“consecutive”就用DENSE_RANK说“跳过位置”就用RANK说“每行唯一”就用ROW_NUMBER。输出顺序确定吗除了排名用的排序键再加一个唯一键做次级排序防止分页或并行执行时顺序漂移。这三条看起来简单但每一条都是从真实事故里总结出来的。写SQL这行很多问题不是语法不会而是语义没想清楚。178题就是一道特别经典的“语义题”搞清楚它后面所有排名需求都会变得特别顺。