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

MSSQL 专栏收录该内容
0 篇文章 0 订阅

前言

原案例是由于同事要对一个表的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

执行结果:
排序结果
可以看到,在有两个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

执行结果:
依照科目分类排序

更新排序序号

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

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 的值是一直的):
更新排名

感言

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

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

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

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

  • 2
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 技术黑板 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值