SQL Server 巧用 ROW_NUMBER() 與 PARTITION BY 實現分組取 Top N

場景

學生成績表，需查詢「每科成績前 3 名」。

建立測試資料

CREATE TABLE #Score (Name VARCHAR(20), Subject VARCHAR(20), Score INT);
INSERT INTO #Score VALUES
('張三','語文',98),('張三','數學',80),('張三','英語',90),
('李四','語文',88),('李四','數學',86),('李四','英語',88),
('李明','語文',60),('李明','數學',86),('李明','英語',88),
('林風','語文',74),('林風','數學',99),('林風','英語',59),
('嚴明','英語',96);

核心查詢

SELECT Subject, Name, Score
FROM (
    SELECT Subject, Name, Score,
           ROW_NUMBER() OVER (PARTITION BY Subject ORDER BY Score DESC) AS rn
    FROM #Score
) t
WHERE rn <= 3
ORDER BY Subject, rn;

語法解析

ROW_NUMBER() OVER (PARTITION BY <分組欄位> ORDER BY <排序欄位>)

• PARTITION BY Subject：依科目分組。
• ORDER BY Score DESC：組內按分數遞減排序。
• 函數回傳組內連續唯一序號 (1,2,3...)。

清理

DROP TABLE #Score;

延伸應用

• 每類商品銷售前 5 名。
• 每部門薪資最高 3 人。
• 搭配 RANK() 或 DENSE_RANK() 處理並列名次。