SQL Server 分組取前 N 條記錄的高效寫法

業務場景

訂單表 Orders (OrderId, UserId, Amount, CreatedAt)，查詢每位用戶最新的 3 筆訂單。

方案一：ROW_NUMBER() 分組排序 (通用標準)

WITH Ranked AS (
    SELECT 
        OrderId, UserId, Amount, CreatedAt,
        ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY CreatedAt DESC) AS rn
    FROM Orders
)
SELECT OrderId, UserId, Amount, CreatedAt
FROM Ranked
WHERE rn <= 3
ORDER BY UserId, CreatedAt DESC;

✅ 優點：語義清晰、支援複雜排序、SQL Server 2005+ 全版本支援。
💡 索引建議：INDEX(UserId, CreatedAt DESC) INCLUDE(OrderId, Amount) 實現索引覆蓋。

方案二：CROSS APPLY (SQL Server 2005+ 專用，性能更優)

SELECT u.UserId, ca.OrderId, ca.Amount, ca.CreatedAt
FROM Users u
CROSS APPLY (
    SELECT TOP 3 OrderId, Amount, CreatedAt
    FROM Orders o
    WHERE o.UserId = u.UserId
    ORDER BY CreatedAt DESC
) ca;

✅ 優點：執行計劃為 Nested Loops，外層驅動表 Users，內層 TOP 3 早期終止，大數據量下顯著快於窗口函數。
💡 適用：驅動表 (用戶) 數據量可控，從表 (訂單) 有對應索引。

方案三：舊版本相關子查詢 (SQL Server 2000 兼容)

SELECT o1.OrderId, o1.UserId, o1.Amount, o1.CreatedAt
FROM Orders o1
WHERE (
    SELECT COUNT(*) 
    FROM Orders o2 
    WHERE o2.UserId = o1.UserId AND o2.CreatedAt >= o1.CreatedAt
) <= 3
ORDER BY o1.UserId, o1.CreatedAt DESC;

❌ 缺點：O(N²) 相關子查詢，大數據量極慢，僅供歷史版本應急。

性能對比實測 (100萬訂單，1萬用戶)

擴展：取每組最後一條 (Top 1)

-- 方案 A: ROW_NUMBER() = 1
-- 方案 B: CROSS APPLY TOP 1
-- 方案 C: 聚合子查詢 (僅需非聚合列少時)
SELECT o.* 
FROM Orders o
JOIN (
    SELECT UserId, MAX(CreatedAt) AS max_dt FROM Orders GROUP BY UserId
) m ON o.UserId = m.UserId AND o.CreatedAt = m.max_dt;

最佳實踐

1. SQL Server 2005+ 首選 CROSS APPLY TOP N，性能最優
2. 需支援舊版本或可移植性時用 ROW_NUMBER()
3. 必須建立 (分組列, 排序列 DESC) 組合索引
4. 驅動表 (分組維度) 建議為獨立維度表而非大表 DISTINCT