SQL 版本對比查詢：兩版本計劃數據差異分析的高效寫法

業務場景

表結構：`Plan(Id, SeqNo, Name, StartTime, EndTime, Version)`
需求：給定兩個版本號（如 200 和 201），對比兩版本中相同 `SeqNo` 的計劃，輸出差異：`SeqNo, Name, StartTime, EndTime, DiffDays`，其中 `DiffDays = V201.StartTime - V200.StartTime`。
關鍵難點：版本 200 有 SeqNo 1,2,3；版本 201 有 SeqNo 1,2,4。結果需包含所有 1,2,3,4 四條記錄，缺失版本的字段顯示為 NULL 或 0。

核心思路：全外連接 + 條件聚合

這是典型的「行轉列 + 版本對比」問題。標準 SQL 解法：

方案一：FULL OUTER JOIN（標準 SQL，Oracle/PostgreSQL/SQL Server 支持）

SELECT 
    COALESCE(v200.SeqNo, v201.SeqNo) AS SeqNo,
    COALESCE(v200.Name, v201.Name) AS Name,
    v200.StartTime AS StartTime_V200,
    v201.StartTime AS StartTime_V201,
    DATEDIFF(day, v200.StartTime, v201.StartTime) AS DiffDays
FROM 
    (SELECT * FROM Plan WHERE Version = 200) v200
FULL OUTER JOIN 
    (SELECT * FROM Plan WHERE Version = 201) v201
ON v200.SeqNo = v201.SeqNo
ORDER BY SeqNo;

方案二：條件聚合（MySQL 8.0+/SQL Server/Oracle/PostgreSQL 通用，性能最優）

SELECT 
    SeqNo,
    MAX(CASE WHEN Version = 200 THEN Name END) AS Name_V200,
    MAX(CASE WHEN Version = 201 THEN Name END) AS Name_V201,
    MAX(CASE WHEN Version = 200 THEN StartTime END) AS StartTime_V200,
    MAX(CASE WHEN Version = 201 THEN StartTime END) AS StartTime_V201,
    DATEDIFF(day, 
        MAX(CASE WHEN Version = 200 THEN StartTime END),
        MAX(CASE WHEN Version = 201 THEN StartTime END)
    ) AS DiffDays
FROM Plan
WHERE Version IN (200, 201)
GROUP BY SeqNo
ORDER BY SeqNo;

方案三：MySQL 5.7 / 不支持 FULL JOIN 的數據庫（UNION + GROUP BY）

SELECT 
    SeqNo,
    MAX(Name) AS Name,  -- 假設同 SeqNo 名稱相同
    MAX(CASE WHEN Version = 200 THEN StartTime END) AS StartTime_V200,
    MAX(CASE WHEN Version = 201 THEN StartTime END) AS StartTime_V201,
    DATEDIFF(
        MAX(CASE WHEN Version = 201 THEN StartTime END),
        MAX(CASE WHEN Version = 200 THEN StartTime END)
    ) AS DiffDays
FROM (
    SELECT SeqNo, Name, StartTime, Version FROM Plan WHERE Version = 200
    UNION ALL
    SELECT SeqNo, Name, StartTime, Version FROM Plan WHERE Version = 201
) t
GROUP BY SeqNo
ORDER BY SeqNo;

窗口函數進階：LEAD/LAG 實現版本間差異（單表掃描）

如果版本號連續且有序，可用窗口函數避免自連接：

WITH Versioned AS (
    SELECT 
        SeqNo, Name, StartTime, Version,
        LEAD(StartTime) OVER (PARTITION BY SeqNo ORDER BY Version) AS NextVersionStartTime,
        LEAD(Version) OVER (PARTITION BY SeqNo ORDER BY Version) AS NextVersion
    FROM Plan
    WHERE Version IN (200, 201)
)
SELECT 
    SeqNo, Name, StartTime, NextVersionStartTime,
    DATEDIFF(day, StartTime, NextVersionStartTime) AS DiffDays
FROM Versioned
WHERE Version = 200 AND NextVersion = 201
UNION ALL
-- 處理只在 201 存在的 SeqNo
SELECT SeqNo, Name, NULL, StartTime, NULL
FROM Plan WHERE Version = 201
AND SeqNo NOT IN (SELECT SeqNo FROM Plan WHERE Version = 200)
ORDER BY SeqNo;

性能優化建議

1. 索引策略：創建復合索引 `INDEX idx_version_seqno (Version, SeqNo) INCLUDE (Name, StartTime, EndTime)`
2. 避免 SELECT *：只查詢需要的列
3. 參數化查詢：版本號使用參數而非硬編碼
4. 分區表：若數據量極大，按 Version 分區

不同數據庫的日期差異函數

總結

版本對比查詢的最佳實踐：

1. 首選條件聚合（CASE WHEN + GROUP BY）：單表掃描、通用性強、性能最優
2. 次選 FULL OUTER JOIN：語義清晰，但 MySQL 不支持
3. 窗口函數 LEAD/LAG：適用於多版本連續對比趨勢分析
4. 關鍵索引：`(Version, SeqNo)` 覆蓋索引是性能關鍵

根據數據庫版本和數據量選擇方案，條件聚合是最通用的「銀彈」。