程式設計師之家

SQL 2005 花了不少功夫替 T-SQL加了些功能, 首先來看這四個函數

ROW_NUMBER()
RANK()
DENSE_RANK()
NTILE(n)

這四個函數 說起來使用的方法都一樣, 先來看 ROW_NUMBER( )

USE Northwind

Select ROW_NUMBER() OVER (Order By OrderId) as RNO,
OrderID, OrderDate
From Orders

執行後的結果:

多了一個欄位 rno, 這一個欄位會按照 OVER ( Order By ...) 的順序產生出列號, 而這個列號, 不會重覆, 這一個功能可以說是SQL 2000之前一堆人想盡辦法在程式中藥家的東西, 因為有了列號, 要做分頁就容易了, 而這個功能 T-SQL直到 SQL 2005 才把牠放進去, 這裡就先來看這個語法

Select ROW_NUMBER() OVER (Order By OrderId) as RNO,
OrderID, OrderDate
From Orders

ROW_NUMBER( ) 式函數名稱
使用這些函數的時候 (ROW_NUMBER,RANK, DENSE_RANK, NTILE), 後面一定跟著 OVER ( .... )
OVER (Order By OrderId) :產生列號的依據
ROW_NUMBER( )產生的順序就根據 Order By OrderId 因此會依據 OrderId 由小排到大將數字產生

而在 Over ( ....) 這個括弧中有兩個部分, 再看下一個例子:

Select CategoryID, ProductID, ProductName, UnitPrice,
RANK() OVER (Partition By CategoryId Order By UnitPrice DESC) as rnk
From Products p

上述SQL的句子中:

OVER (Partition By CategoryId Order By UnitPrice DESC)

Partition By CategoryId 以CategoryId作為分隔, 也就是, CategoryId一樣的資料
重新排名, 所以可以根據個別的類別, 排出價格的順序

DENSE_RANK和RANK很類似, 不同的是, 如果有兩個第二名, RANK函數 下一個人會跳到第四名
而DENSE_RANK數字都會連續, 即使有兩個第二名, 下一個人依然是第三名
可以比較以下兩個SQL句子
 

Select CategoryID, ProductID, ProductName, UnitPrice,
RANK() OVER (Partition By CategoryId Order By UnitPrice DESC) as rnk
From Products

 

Select CategoryID, ProductID, ProductName, UnitPrice,
DENSE_RANK() OVER (Partition By CategoryId Order By UnitPrice DESC) as rnk
From Products

NTILE(n) 這個函數則是平均等分成 n 等份, 例如, 要將Products 中的資料依據價格, 等分成五等份(最貴, 貴, 一般, 便宜, 最便宜)

Select ProductID, ProductName, UnitPrice,
NTILE(5) Over (Order By UnitPrice Desc) as Kind
From Products p

這樣即可達成, 簡單介紹了這四個函數

ROW_NUMBER( ), RANK( ), DENSE_RANK( ), NTILE(5)