--> 测试数据: @s
declare @s table (ID int,NAME varchar(1),SCORE int)
insert into @s
select 1,'A',86 union all
select 2,'B',77 union all
select 3,'C',94 union all
select 4,'D',77select * from
(select id=(select count(distinct score) from @s where SCORE>=a.SCORE),name,SCORE from @s a)b
order by id
insert into @tb select 1,'a',86
insert into @tb select 2,'b',77
insert into @tb select 3,'c',94
insert into @tb select 4,'d',77
insert into @tb select 6,'e',37select rank() over(order by score desc) as orderid,name,score
from @tb--1 c 94
--2 a 86
--3 b 77
--3 d 77
--5 e 37select dense_rank() over(order by score desc) as orderid,name,score
from @tb--1 c 94
--2 a 86
--3 b 77
--3 d 77
--4 e 37
declare @tb table([ID] int,[NAME] nvarchar(1),[SCORE] int)
Insert @tb
select 1,N'A',86 union all
select 2,N'B',77 union all
select 3,N'C',94 union all
select 4,N'D',77
Select px=(select count(distinct score)+1 from @tb where [score] > t.[score]),t.* from @tb t order by 1
/*
px ID NAME SCORE
----------- ----------- ---- -----------
1 3 C 94
2 1 A 86
3 2 B 77
3 4 D 77(4 row(s) affected)
*/