27,579
社区成员
发帖
与我相关
我的任务
分享
update TB1 set ranking =b.rn
from
(select *,ROW_NUMBER() over ( order by score desc) as rn from TB1) b
where TB1.name=b.name and TB1.score=b.score
go
select * from TB1
-- name score ranking
--CrazyFor 96840 16
--DBA_Huangzj 196706 6
--dawugui 432604 2
--fredrickhu 296832 4
--Haiwer 105563 14
--happyflystone 138420 8
--htl258 125853 9
--josy 95658 17
--liangCK 115245 11
--libin_ftsafe 260840 5
--paoluo 106146 13
--pengdali 374373 3
--qianjin036a 111430 12
--roy_88 140446 7
--txlicenhe 102159 15
--wufeng4552 121166 10
--zjcxc 878276 1
update tb1 set ranking=a.tempRanking from (
select *,ROW_NUMBER() over(order by score desc) as tempRanking from tb1
) a where tb1.name=a.name and tb1.score=a.score
update tb1 set ranking=a.tempRanking from (
select *,rank() over(order by score desc) as tempRanking from tb1
) a where tb1.name=a.name and tb1.score=a.score
update tb1 set ranking=a.tempRanking from (
select *,dense_rank() over(order by score desc) as tempRanking from tb1
) a where tb1.name=a.name and tb1.score=a.score
第一種 不重複排名
第二種 跳越排名
第三種 連續排名
create table tb1
(
name varchar(32),
score int,
ranking int,
)
GO
insert into tb1 (name, score) values ('CrazyFor' , 96840 )
insert into tb1 (name, score) values ('DBA_Huangzj' , 196706 )
insert into tb1 (name, score) values ('dawugui' , 432604 )
insert into tb1 (name, score) values ('fredrickhu' , 296832 )
insert into tb1 (name, score) values ('Haiwer' , 105563 )
insert into tb1 (name, score) values ('happyflystone' , 138420 )
insert into tb1 (name, score) values ('htl258' , 125853 )
insert into tb1 (name, score) values ('josy' , 95658 )
insert into tb1 (name, score) values ('liangCK' , 115245 )
insert into tb1 (name, score) values ('libin_ftsafe' , 260840 )
insert into tb1 (name, score) values ('paoluo' , 106146 )
insert into tb1 (name, score) values ('pengdali' , 374373 )
insert into tb1 (name, score) values ('qianjin036a' , 111430 )
insert into tb1 (name, score) values ('roy_88' , 140446 )
insert into tb1 (name, score) values ('txlicenhe' , 102159 )
insert into tb1 (name, score) values ('wufeng4552' , 121166 )
insert into tb1 (name, score) values ('zjcxc' , 878276 )
select * from tb1
---