[SQL].取上下筆資料的Sample

分享 T-SQL 的句子, 可以用來取上/下一筆的資料.

附註: row_number() over(...) 這個指令, 要 sql server 2005 以上的版本才有支援.

--// 取上一筆

select top 1 * from (select 欄位1,

row_number() over(order by 欄位2 desc) as rownum

from Table1) myData

where rownum < (

select rownum from (select 欄位1,

row_number() over(order by 欄位2 desc) as rownum

from Table1) myData

where id=?

)

order by rownum desc

--// 取下一筆

select top 1 * from (select 欄位1,

row_number() over(order by 欄位2 desc) as rownum

from Table1) myData

where rownum > (

select rownum from (select 欄位1,

row_number() over(order by 欄位2 desc) as rownum

from Table1) myData

where id=?

)

--// 取自己 rownum

select rownum from (select 欄位1,

row_number() over(order by 欄位2 desc) as rownum

from Table1) myData

where id=?

特別感謝: 鈺鈞告訴我有這個指令.