SQL 分組取每組最新日期記錄：標準寫法與效能優化

需求描述

資料表含 id, date, value1, value2。依 id 分組，取每組 date 最大 (最新) 的那筆完整記錄 (含 value1, value2)。

方案一：關聯子查詢 (相容性最好)

SELECT t1.id, t1.date, t1.value1, t1.value2
FROM table_name t1
INNER JOIN (
    SELECT id, MAX(date) AS max_date
    FROM table_name
    GROUP BY id
) t2 ON t1.id = t2.id AND t1.date = t2.max_date;

注意：若同組同日期有多筆，會全部返回。需進一步去重可加 ROW_NUMBER()。

方案二：窗口函數 (現代標準、可讀性高)

WITH ranked AS (
    SELECT id, date, value1, value2,
           ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS rn
    FROM table_name
)
SELECT id, date, value1, value2
FROM ranked
WHERE rn = 1;

• 支援：PostgreSQL, SQL Server, Oracle, MySQL 8.0+, SQLite 3.25+。
• ROW_NUMBER() 保證每組僅取一筆；若需保留同日期並列，改用 RANK() 或 DENSE_RANK()。

方案三：側向連結 / CROSS APPLY (針對大資料量優化)

-- PostgreSQL / SQL Server
SELECT t.id, ca.date, ca.value1, ca.value2
FROM (SELECT DISTINCT id FROM table_name) t
CROSS JOIN LATERAL (
    SELECT date, value1, value2
    FROM table_name
    WHERE id = t.id
    ORDER BY date DESC
    LIMIT 1
) ca;

利用索引 (id, date DESC) 可極快定位每組最新筆，避免全表掃描與排序。

索引建議

CREATE INDEX idx_id_date ON table_name (id, date DESC);
-- 覆蓋索引 (若表較窄)
CREATE INDEX idx_id_date_cover ON table_name (id, date DESC) INCLUDE (value1, value2);

MySQL 5.7 及舊版本替代寫法

SELECT t1.*
FROM table_name t1
JOIN (
    SELECT id, MAX(date) AS max_date
    FROM table_name
    GROUP BY id
) t2 ON t1.id = t2.id AND t1.date = t2.max_date;

總結：現代資料庫首選窗口函數 (方案二)；大資料量且有索引支援時，側向連結 (方案三) 效能最優。