| 函數 | 行為 | 並列處理 | 編號連續性 |
|---|---|---|---|
ROW_NUMBER() | 分區內唯一遞增序號 | 不產生並列，強制 1,2,3... | 連續無間隙 |
RANK() | 並列同名次，下一名次跳過並列數 | 並列同名次 (如 1,1,3) | 有間隙 |
DENSE_RANK() | 並列同名次，下一名次緊接 | 並列同名次 (如 1,1,2) | 連續無間隙 |
FUNCTION() OVER ( [PARTITION BY partition_column_list] ORDER BY order_column_list )
PARTITION BY：分組，每組獨立編號 (可省略，視為單一分區)。ORDER BY：必填，決定編號順序。CREATE TABLE rankorder(orderid INT, qty INT);
INSERT rankorder VALUES (30001,10),(10001,10),(10006,10),(40005,10),
(30003,15),(30004,20),(20002,20),(20001,20),
(10005,30),(30007,30),(40001,40),(30007,30);
SELECT orderid, qty,
ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
RANK() OVER(ORDER BY qty) AS rank,
DENSE_RANK() OVER(ORDER BY qty) AS denserank
FROM rankorder
ORDER BY qty;
結果：
| orderid | qty | rownumber | rank | denserank |
|---|---|---|---|---|
| 30001 | 10 | 1 | 1 | 1 |
| 10001 | 10 | 2 | 1 | 1 |
| 10006 | 10 | 3 | 1 | 1 |
| 40005 | 10 | 4 | 1 | 1 |
| 30003 | 15 | 5 | 5 | 2 |
| 30004 | 20 | 6 | 6 | 3 |
| 20002 | 20 | 7 | 6 | 3 |
| 20001 | 20 | 8 | 6 | 3 |
| 10005 | 30 | 9 | 9 | 4 |
| 30007 | 30 | 10 | 9 | 4 |
| 30007 | 30 | 11 | 9 | 4 |
| 40001 | 40 | 12 | 12 | 5 |
觀察：qty=10 四筆並列，ROW_NUMBER 強制 1-4，RANK 全部 1，下一名次跳到 5；DENSE_RANK 全部 1，下一名次為 2。
SELECT orderid, qty,
ROW_NUMBER() OVER(ORDER BY qty, orderid) AS rownumber,
RANK() OVER(ORDER BY qty, orderid) AS rank,
DENSE_RANK() OVER(ORDER BY qty, orderid) AS denserank
FROM rankorder
ORDER BY qty, orderid;
因 orderid 唯一，三函數結果完全一致 (1-12 連續)，無並列。
ROW_NUMBER() 配合 CTE (WHERE rn BETWEEN @start AND @end)。ROW_NUMBER() OVER (PARTITION BY key ORDER BY date DESC) = 1。RANK() (並列同名次) 或 DENSE_RANK() (連續名次)。WHERE rn <= N。-- ROW_NUMBER: 依 SalesYTD 排名業務員
SELECT c.FirstName, c.LastName,
ROW_NUMBER() OVER(ORDER BY s.SalesYTD DESC) AS 'Row Number',
s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
JOIN Person.Contact c ON s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;
-- RANK: 依庫存量量對產品排名 (按 LocationID 分區)
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity,
RANK() OVER(PARTITION BY i.LocationID ORDER BY i.Quantity) AS RANK
FROM Production.ProductInventory i
JOIN Production.Product p ON i.ProductID = p.ProductID
ORDER BY p.Name;
-- DENSE_RANK: 同上但名次連續
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity,
DENSE_RANK() OVER(PARTITION BY i.LocationID ORDER BY i.Quantity) AS DENSE_RANK
FROM Production.ProductInventory i
JOIN Production.Product p ON i.ProductID = p.ProductID
ORDER BY p.Name;
