您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 2005 排序函數實戰：ROW_NUMBER、RANK、DENSE_RANK、NTILE

編輯：關於SqlServer

建立測試資料

CREATE DATABASE demo; GO
USE demo; GO
CREATE TABLE T_User (
    UserId   VARCHAR(32) PRIMARY KEY,
    UserName VARCHAR(64) NOT NULL,
    Score    INT NOT NULL
);
INSERT INTO T_User VALUES
('pd002', N'Steven', 78),
('pd001', N'James',  80),
('pd003', N'Allen',  80),
('pd004', N'Brue',   86),
('pd005', N'Evens',  93);

1. ROW_NUMBER()：連續不重複編號

SELECT ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowId, *
FROM T_User;

RowId	UserId	UserName	Score
1	pd005	Evens	93
2	pd004	Brue	86
3	pd001	James	80
4	pd003	Allen	80
5	pd002	Steven	78

→ 無論分數相同，編號 1,2,3,4,5 連續遞增。

2. RANK()：同分同名次，下一名次跳號

SELECT RANK() OVER (ORDER BY Score DESC) AS RowId, *
FROM T_User;

RowId	UserId	UserName	Score
1	pd005	Evens	93
2	pd004	Brue	86
3	pd001	James	80
3	pd003	Allen	80
5	pd002	Steven	78

→ 兩人同分佔第 3 名，下一名直接跳到第 5 名 (奧運排名規則)。

3. DENSE_RANK()：同分同名次，下一名次不跳號

SELECT DENSE_RANK() OVER (ORDER BY Score DESC) AS RowId, *
FROM T_User;

RowId	UserId	UserName	Score
1	pd005	Evens	93
2	pd004	Brue	86
3	pd001	James	80
3	pd003	Allen	80
4	pd002	Steven	78

→ 同分佔第 3 名，下一名為第 4 名 (密集排名)。

4. NTILE(n)：分桶/分組

SELECT NTILE(3) OVER (ORDER BY Score DESC) AS Bucket, *
FROM T_User;

Bucket	UserId	UserName	Score
1	pd005	Evens	93
1	pd004	Brue	86
2	pd001	James	80
2	pd003	Allen	80
3	pd002	Steven	78

→ 依分數降序分成 3 組，前兩組各 2 人，最後一組 1 人。

進階：PARTITION BY 分組排序

SELECT 
    UserName, Score,
    ROW_NUMBER() OVER (PARTITION BY Dept ORDER BY Score DESC) AS RankInDept
FROM T_User;

應用場景對照

函數	典型用途
ROW_NUMBER	分頁、去重 (保留每組第一筆)、產生連續流水號
RANK	競賽排名、銷售業績排名 (需跳號)
DENSE_RANK	薪資等級、績效等第 (不跳號)
NTILE	百分位數分析、分桶抽樣、四分位數

善用視窗函數，複雜排名邏輯一行搞定，效能遠優於游標/臨時表。