SQL Server 2000 分組取最新記錄：ROW_NUMBER() 替代方案與實戰技巧

SQL Server 2000 不支援 ROW_NUMBER()、CROSS APPLY 等現代視窗函數，導致「分組後取每組最新日期的完整行」成為經典難題。本文提供三種相容 SQL Server 2000 的解法，並附效能比較建議。

場景重述

資料表 Records(id, date, value1, value2)，需依 id 分組，取每組 date 最大那一行的 value1, value2。

-- 期望結果
id  date        value1  value2
1   2015-01-02  210     90
2   2015-01-04  50      50
3   2015-01-01  100     100

解法一：相關子查詢（最直觀，中小資料量適用）

SELECT r1.id, r1.date, r1.value1, r1.value2
FROM Records r1
WHERE r1.date = (
    SELECT MAX(r2.date)
    FROM Records r2
    WHERE r2.id = r1.id
);

⚠️ 若同組存在相同最大日期的多行，會全部返回（可再加 TOP 1 或業務鍵去重）。

解法二：衍生表 JOIN（效能較佳，利用索引）

SELECT r.id, r.date, r.value1, r.value2
FROM Records r
INNER JOIN (
    SELECT id, MAX(date) AS max_date
    FROM Records
    GROUP BY id
) g ON r.id = g.id AND r.date = g.max_date;

建議在 (id, date) 建立複合索引，子查詢與 JOIN 均能用上 Index Seek。

解法三：NOT EXISTS 反向邏輯（避免重複行，語意清晰）

SELECT r1.id, r1.date, r1.value1, r1.value2
FROM Records r1
WHERE NOT EXISTS (
    SELECT 1 FROM Records r2
    WHERE r2.id = r1.id
      AND r2.date > r1.date
);

語意：「找出沒有更晚日期的同組記錄」，天然去重，執行計劃常優於子查詢。

效能調優建議

• 索引策略：CREATE INDEX IX_Records_Id_Date ON Records(id, date DESC) INCLUDE (value1, value2); 覆蓋查詢，避免 Key Lookup。
• 統計資料更新：SQL Server 2000 需手動 UPDATE STATISTICS Records; 確保優化器估算準確。
• 資料量 > 100 萬：考慮建立索引視圖（Indexed View）物化分組最大日期，查詢直接讀視圖。

遷移提醒

若環境允許升級至 SQL Server 2005+，請直接使用 ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC)，語法簡潔且執行計劃最優。但維護舊系統時，上述三種解法皆為生產級可用方案。