SQL 分組取每組最大值完整攻略：三大經典模式、效能對比與現代寫法 (ROW_NUMBER/APPLY/側邊表)

核心問題：分組 Top-N 查詢

給定銷售表 Sales(Year, Product, Qty)，求每年銷量最高的產品：

期望結果：

模式一：NOT EXISTS 相關子查詢 (經典、通用、易讀)

SELECT s1.*
FROM Sales s1
WHERE NOT EXISTS (
    SELECT 1 FROM Sales s2
    WHERE s2.Year = s1.Year
      AND s2.Qty > s1.Qty
);

• 邏輯：找出「不存在同組更大 Qty」的行
• 優點：ANSI SQL 通用、無需窗口函數支援、語義直觀
• 缺點：大資料量效能差 (相關子查詢逐行執行)、平手時返回多行
• 適用：小表、臨時查詢、不支援窗口函數的舊版本

模式二：內連接衍生表聚合 (傳統高效、平手全取)

SELECT s1.*
FROM Sales s1
INNER JOIN (
    SELECT Year, MAX(Qty) AS MaxQty
    FROM Sales
    GROUP BY Year
) s2 ON s1.Year = s2.Year AND s1.Qty = s2.MaxQty;

• 邏輯：先算各組最大值，再回表關聯
• 優點：可用索引 (Year, Qty) 加速聚合與關聯、平手情況自動返回所有並列第一
• 缺點：需掃描表兩次 (聚合 + 關聯)、若選取欄位多需回表
• 適用：中大表、需平手全取、索引友善

模式三：自連接 (Self-Join) — 邏輯對偶，效能同模式二

SELECT s1.*
FROM Sales s1
LEFT JOIN Sales s2 
    ON s1.Year = s2.Year AND s2.Qty > s1.Qty
WHERE s2.Year IS NULL;  -- 找不到更大的 = 最大

• 本質上與 NOT EXISTS 等價，優化器常生成相同計畫

場景二：主鍵表 — 取每組最新記錄 (依日期/遞增鍵)

表 CarLog(ID, CarID, DateChange)，求每車最新記錄：

期望：ID 2 和 4

NOT EXISTS 寫法

SELECT t.*
FROM CarLog t
WHERE NOT EXISTS (
    SELECT 1 FROM CarLog
    WHERE CarID = t.CarID
      AND DateChange > t.DateChange
);

現代最佳寫法：窗口函數 ROW_NUMBER() (SQL Server 2005+/Oracle 10g+/PostgreSQL 8.4+/MySQL 8.0+)

WITH Ranked AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY Year ORDER BY Qty DESC) AS rn
    FROM Sales
)
SELECT Year, Product, Qty
FROM Ranked
WHERE rn = 1;

進階：平手處理策略

完整平手保留版 (RANK)

WITH Ranked AS (
    SELECT 
        *,
        RANK() OVER (PARTITION BY Year ORDER BY Qty DESC) AS rk
    FROM Sales
)
SELECT Year, Product, Qty
FROM Ranked
WHERE rk = 1;

效能對比：百萬行測試 (SQL Server 2019)

結論：窗口函數單次掃描、單次排序、無回表，碾壓傳統寫法。

場景三：自連接查詢 — 經過多個節點的實體 (如車次經過南京 AND 江蘇)

表 PassStation(TrainNo, SeqNo, Station)

SELECT DISTINCT r1.TrainNo
FROM PassStation r1
INNER JOIN PassStation r2 
    ON r1.TrainNo = r2.TrainNo
WHERE r1.Station = '南京' 
  AND r2.Station = '江蘇';

• 本質是關係除法：找出同時滿足多個條件的實體群組
• 擴展至 N 個條件：連接 N 次或改用聚合 HAVING COUNT(DISTINCT Station) = N

現代替代：CROSS APPLY / OUTER APPLY (SQL Server 專用，單行子查詢優化)

-- 取每組最新記錄，含完整欄位，無需回表
SELECT ca.*
FROM (
    SELECT DISTINCT CarID FROM CarLog
) d
CROSS APPLY (
    SELECT TOP 1 * 
    FROM CarLog 
    WHERE CarID = d.CarID 
    ORDER BY DateChange DESC
) ca;

• 優勢：對每個分組只掃描索引前 1 行、極適合「分組 Top 1 且需完整欄位」
• 索引需求：INDEX IX_CarLog_CarID_DateChange (CarID, DateChange DESC)

選型決策矩陣

實戰範例：訂單每客戶最新一單完整明細

-- 現代寫法 (SQL Server 2012+)
WITH LatestOrders AS (
    SELECT 
        o.*,
        ROW_NUMBER() OVER (PARTITION BY o.CustomerID ORDER BY o.OrderDate DESC, o.OrderID DESC) AS rn
    FROM Orders o
    WHERE o.OrderDate >= DATEADD(year, -1, GETDATE())  -- 近一年熱數據
)
SELECT 
    CustomerID, OrderID, OrderDate, TotalAmount, Status
FROM LatestOrders
WHERE rn = 1
ORDER BY CustomerID;

-- 索引建議
CREATE INDEX IX_Orders_CustomerID_OrderDate 
ON Orders (CustomerID, OrderDate DESC, OrderID DESC)
INCLUDE (TotalAmount, Status);  -- 覆蓋索引，避免回表

總結

• 窗口函數 (ROW_NUMBER/RANK) 是現代 SQL 分組 Top-N 的標準答案：語法清晰、效能最優、平手語義精確可控
• CROSS APPLY TOP 1 是 SQL Server 獨有的覆蓋索引友善利器，適合「需完整欄位」場景
• NOT EXISTS / 內連接聚合 僅作為舊版本相容或教學示範保留
• 索引設計 永遠是 (分組欄位, 排序欄位 DESC) + INCLUDE(選取欄位) 達到極致

掌握這套模式，讓分組 Top-N 查詢從「效能殺手」變成「毫秒級常規操作」。