MSSQL將查詢結果加上序號(ROW_NUMBER,RANK,DENSE_RANK)

1. ROW_NUMBER依指定的欄位排序，並逐筆加上流水號ROWID
   SELECT  ROW_NUMBER() OVER(ORDER BY CustomerID) AS ROWID ,* FROM Orders
2. RANK依指定的欄位排序，相同的資料流水號相同，下一筆不同資料的流水號會【跳脫】
   SELECT  RANK() OVER(ORDER BY CustomerID) AS ROWID ,* FROM Orders
3. DENSE_RANK依指定的欄位排序，相同的資料流水號相同，下一筆不同資料的流水號會【不跳脫】
   SELECT  DENSE_RANK() OVER(ORDER BY CustomerID) AS ROWID ,* FROM Orders
4. 分頁範例
   SELECT * FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY CustomerID ) AS ROWID,* FROM Orders ) AS tmp WHERE ROWID BETWEEN 21 AND 30 ORDER BY CustomerID

參考來源：topcat 姍舞之間的極度凝聚