2018年6月25日 星期一

[MSSQL] 使用ROW_NUMBER() 替查詢結果加上序號

依自訂排序條件加上序號:

SELECT ROW_NUMBER() OVER(ORDER BY [ProductID]) AS ROWID,* FROM Products

依預設排序加上序號:

SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS ROWID,* FROM Products

依據相同欄位內容加上序號:

SELECT ROW_NUMBER() OVER(PARTITION BY [CategoryID] ORDER BY ORDER BY [ProductID]) AS ROWID,* FROM Products

依據欄位內容加上序號,並取最近一筆資料:

SELECT * FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY [CategoryID] ORDER BY ORDER BY [CreateTime]) AS ROWID, *
FROM Products) VM
WHERE VM.ROWID=1

沒有留言:

張貼留言