作者jkly1846 (ajaxjsp)
看板Database
標題Re: [SQL ] MSSQL2005 怎樣取得某行的排名名次
時間Tue Jun 21 14:22:08 2011
引述《syamp (fsd)》之銘言：
: 你的意思是這樣 ?
: select * from
: (select row_number() over(order by integral desc) as rownum
: from igsns_listlog
: where GameID = '101') v1
: where v1.UID = '51e2c238-8be7-4043-9113-9ec500abc928'
HI 你好
那樣會出錯
因該是
select * from
(select row_number() over(order by integral desc) as rownum,UID,GameID
from igsns_listlog
where GameID = '101') v1
where v1.UID = '51e2c238-8be7-4043-9113-9ec500abc928'
這樣結果才能秀出UID
我後來有自己試出來 這是一個可以列出使用者名次兼前後各4名次的T-SQL
@MyInput_UID nvarchar(50)
As
WITH MyIGSNS_ListLog AS
(
SELECT ROW_NUMBER() OVER(ORDER BY [Integral] DESC) AS
rownum,UID,AccountID,Nickname,GameID,Integral
FROM IGSNS_ListLog
join IGSNS_UserData
on IGSNS_ListLog.UID = IGSNS_UserData.GUID
)
SELECT *
FROM MyIGSNS_ListLog
where rownum<((SELECT rownum
FROM MyIGSNS_ListLog
where uid=@MyInput_UID)+5)
and rownum>((SELECT rownum
FROM MyIGSNS_ListLog
where uid=@MyInput_UID)-5)
提拱給板上需要的人
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 61.220.207.97