记一次 MSSQL [SQLServer] 中更新排序序号的问题

时间：2024-04-01 14:03:16

前言

原案例是由于同事要对一个表的XH（序号）字段根据另一个字段的大小顺序进行更新，同事原先的写法应该是通过SELECT COUNT(*)+1 同时WHERE条件中将排序字段进行对比，从而计算得到的序号，然后用得到的序号对字段进行更新，这个方法在遇到了排序字段值相同的情况，它就得到了相同的序号，下一个就跳过了，就比如考试一样，有两个100分，那么99分就是第3名，两个100分并列第1名，同事想要的结果是直接按顺序来，也就是说，一个100分第1名，另一个第2名，99分第3名
本人其实对于MSSQL也是小面一个，对于同事的需求联想到Oracle数据库的rownum伪列，就去百度了一下发现果然有类似的写法，因此记录一下，以备后用

知识点概览

其实在MSSQL中，和Oracle一样提供了多个用于获取排序序号的函数
通过百度，得到以下函数

  • RANK
  • DENSE_RANK
  • ROW_NUMBER

下面来创建一个简单的学生分数表来验证一下这些个函数

验证与实战

  • 首先我们创建一个表，表里包含了 ID、科目、分数、排名 字段，并插入一些测试数据，由于后续还要更新排名字段，这里在插入测试数据时，就排名的值就依照 ID 的值进行插入，也就是错误的排名
--创建测试表
create table S_SCORE (id int,[科目] varchar(20),[分数] int,[排名] int);

--插入测试数据
insert into S_SCORE values (1,'数学',99,1);
insert into S_SCORE values (2,'数学',89,2);
insert into S_SCORE values (3,'数学',69,3);
insert into S_SCORE values (4,'数学',29,4);
insert into S_SCORE values (5,'数学',29,5);
insert into S_SCORE values (6,'数学',59,6);
insert into S_SCORE values (7,'语文',55,7);
insert into S_SCORE values (8,'语文',42,8);
insert into S_SCORE values (9,'语文',76,9);
insert into S_SCORE values (10,'语文',100,10);
insert into S_SCORE values (11,'语文',100,11);
insert into S_SCORE values (12,'语文',98,12);
  • 先忽略科目这个字段，我们看一下这几个函数有什么区别（注意：分数相同的情况，这几个函数有不同的表现）
select id,[分数],
RANK() over (order by [分数] desc) AS RANK_SORT,
DENSE_RANK() over (order by [分数] desc) AS DENSE_RANK_SORT,
ROW_NUMBER() over (order by [分数] desc) AS ROW_NUMBER_SORT
from S_SCORE

执行结果：
记一次 MSSQL [SQLServer] 中更新排序序号的问题
可以看到，在有两个100的情况下，对第二个100分和99分的处理是不一样的

在RANK函数中，两个100分并列第一，99分排第三，跳过了第二这个名次
在DENSE_RANK函数中，两个100分依旧并列第一，99分排第二
在ROW_NUMER函数中，两个100分虽然并列第一，但还是按顺序排出了第一和第二

  • 现在咱们加上科目这个字段，在这三个函数中，通过over，都可以对科目中的值进行分类排序
select A.*,
RANK() over (partition by [科目] order by [分数] desc) AS RANK_SORT,
DENSE_RANK() over (partition by [科目] order by [分数] desc) AS DENSE_RANK_SORT,
ROW_NUMBER() over (partition by [科目] order by [分数] desc) AS ROW_NUMBER_SORT
from S_SCORE A

执行结果：
记一次 MSSQL [SQLServer] 中更新排序序号的问题

更新排序序号

其实，在了解了以上函数后，这个更新操作似乎就没那么难了
在这里就直接贴上语句了（忽略科目字段）

UPDATE S_SCORE SET [排名]=ROW_NUMBER_SORT
FROM (SELECT A.*,ROW_NUMBER() over (order by [分数] desc) AS ROW_NUMBER_SORT
      FROM S_SCORE A) AA
WHERE S_SCORE.id=AA.id

这可能不是最优的SQL，但本菜鸟已经尽力了
如果有更优的SQL，欢迎大佬回复指点小弟，小弟感激不尽

更新后的结果（原先排名的值与 id 的值是一直的）：
记一次 MSSQL [SQLServer] 中更新排序序号的问题

感言

最近在看《Oracle 编程艺术 深入理解数据库体系结构（第三版）》一书
在书中TOM大佬提出了一个观点，我感触很深

“尽管数据库都已经提供了某个特性，还有人在尝试用笨拙的大型复杂方案来实现相同的功能”

其实在这个案例中，我的同事就用了一段SQL来解决一个函数的问题，其实，这个功能数据库已经帮你实现了，你拿来用就好了

工欲善其事必先利其器，在使用一个数据库前，去了解这个数据库还是很有必要的

相关文章