SELECT USERID,NUM,(SELECT COUNT(1) FROM TB WHERE NUM<=T.NUM) RN FROM TB TSELECT USERID,NUM,(SELECT COUNT(1) FROM TB WHERE NUM>=T.NUM) RN FROM TB T
select 排名=(select count(1) from tb where num>t.num) from tb t where userid=2
也许要从小到大的select 排名=(select count(1) from tb where num<t.num)+1 from tb t where userid=2刚才忘了+1
declare @tb table([userID] int,[Num] int) insert @tb select 1,12 union all select 2,10 union all select 3,14 union all select 4,18 select * ,pm =(select COUNT(1)+1 from @tb where Num <a.Num) from @tb as a order by Num
from tb t
where userid=2
from tb t
where userid=2刚才忘了+1
insert @tb
select 1,12 union all
select 2,10 union all
select 3,14 union all
select 4,18
select * ,pm =(select COUNT(1)+1 from @tb where Num <a.Num) from @tb as a order by Num