單條 SQL 解決「每個 Y 的最新 X」經典問題

問題背景

「每個 Y 的最新 X」是 SQL 開發中的經典需求：例如每位用戶（Y）的最新訂單（X）、每台設備（Y）的最新日誌（X）等。關鍵在於不僅要取得最大值，還要保留該筆記錄的完整資訊（如主鍵 ID）。

建表與測試資料

CREATE TABLE dbo.Tab (
    ID INT NOT NULL IDENTITY(1,1),
    Y VARCHAR(20) NOT NULL,
    X DATETIME NOT NULL
);

INSERT INTO Tab (Y, X) VALUES
('BBB', '2007-10-23 11:11:11'),
('BBB', '2007-10-23 11:11:11'),  -- 同時間，ID 較大者視為最新
('BBB', '2007-10-23 10:10:10'),
('AAA', '2007-10-23 12:12:12'),
('AAA', '2007-10-23 10:10:10'),
('AAA', '2007-10-23 11:11:11');

單條 SQL 解法（NOT EXISTS 相關子查詢）

SELECT ID, Y, X
FROM Tab T
WHERE NOT EXISTS (
    SELECT 1
    FROM Tab T2
    WHERE T2.Y = T.Y
      AND (T2.X > T.X OR (T2.X = T.X AND T2.ID > T.ID))
);

邏輯解析

• 外層遍歷每一筆記錄 T。
• 子查詢檢查：是否存在 同組（Y 相同） 且 時間更晚（X 更大），或 時間相同但 ID 更大 的記錄 T2。
• 若不存在（NOT EXISTS），說明 T 就是該組的「最新 X」。
• 正確處理了「同時間多筆」的情況，以 ID 作為 tie-breaker。

效能優化建議

1. 在 Y 欄位建立索引：CREATE INDEX IX_Tab_Y ON Tab(Y);
2. 若查詢頻繁，可考慮複合索引 (Y, X DESC, ID DESC)，讓資料庫直接定位到每組第一筆。
3. 避免使用 MAX(X) 再 JOIN 回表，因無法正確處理同時間多筆的情況。

替代寫法（視窗函數，SQL Server 2012+ / MySQL 8.0+）

WITH ranked AS (
    SELECT ID, Y, X,
           ROW_NUMBER() OVER (PARTITION BY Y ORDER BY X DESC, ID DESC) AS rn
    FROM Tab
)
SELECT ID, Y, X
FROM ranked
WHERE rn = 1;

視窗函數寫法更直觀、效能通常更佳，是現代 SQL 的首選。

小結

• NOT EXISTS 相關子查詢是經典通用寫法，兼容所有主流資料庫。
• 視窗函數 ROW_NUMBER() 更簡潔高效，推薦用於支援的資料庫版本。
• 索引設計至關重要：(Y, X, ID) 或 (Y, X DESC, ID DESC) 可大幅提升速度。