SQL Server 2005/2008 利用 ROW_NUMBER() 分組取每組前 N 筆資料

情境說明

資料表 scan 記錄科目（km）、考生（kh）、成績（cj）。需依科目與考生分組，取各組成績前 2 高的記錄。

資料表結構

CREATE TABLE [dbo].[scan] (
    [km] [int] NULL,   -- 科目號
    [kh] [int] NULL,   -- 考生號
    [cj] [int] NULL    -- 成績
) ON [PRIMARY];

解法：視窗函數 ROW_NUMBER()

核心邏輯：PARTITION BY 分組，ORDER BY 排序產生序號，外層篩選序號 ≤ N。

SELECT * FROM (
    SELECT a.km, a.kh, cj,
           ROW_NUMBER() OVER (PARTITION BY a.km ORDER BY a.km, a.cj DESC) AS n
    FROM (
        SELECT km, kh, SUM(cj) AS cj
        FROM scan
        GROUP BY km, kh
    ) a
) b
WHERE n <= 2
ORDER BY km, cj DESC;

關鍵點解析

• PARTITION BY a.km：依科目分組
• ORDER BY a.cj DESC：組內按成績降序
• ROW_NUMBER()：為每組記錄產生 1,2,3... 序號
• 外層 WHERE n <= 2 取前 2 筆

結果示意

（圖片展示查詢結果：每個 km 下，各 kh 最高的兩筆 cj 記錄）

擴充應用

• 取前 N 筆：將 n <= 2 改為 n <= N
• 多欄位分組：PARTITION BY km, kh
• 搭配 RANK() 或 DENSE_RANK() 處理並列名次
• 適用於 SQL Server 2005 及以上版本