單一 SQL 展示幾乎所有標準查詢語法：複雜報表查詢範例解析

查詢背景

某預算系統需依預算類型、年度、月份彙總金額，並轉換代碼為可讀名稱，單一陳述式完整呈現。

完整 SQL

SELECT DISTINCT
    SUBSTRING(KMM102.KMM102_BGT_TYPE, 1, 4) AS BGT_TYPE,
    CASE SUBSTRING(KMM102.KMM102_BGT_TYPE, 3, 2)
        WHEN '09' THEN '1'
        WHEN '10' THEN '2'
        WHEN '11' THEN '3'
        ELSE '9'
    END AS BGT_CLASS,
    SUBSTRING(KMM102.KMM102_BGT_TYPE, 1, 2) AS BGT_TEAM,

    ISNULL((
        SELECT SUBSTRING(KMC101.KMC101_NAME, CHARINDEX('-', KMC101.KMC101_NAME) + 1, 12)
        FROM KMC101
        WHERE KMC101.KMC101_TYPE = 'BUDGETCODE'
          AND KMC101.KMC101_CODE = SUBSTRING(KMM102.KMM102_BGT_TYPE, 1, 4) + '00'
    ), '') AS BGT_NAME,

    SUM(ISNULL(KMM101.KMM101_BGT_JAN, 0)) AS BGT_AMT01,
    SUM(ISNULL(KMM101.KMM101_BGT_FEB, 0)) AS BGT_AMT02,
    SUM(ISNULL(KMM101.KMM101_BGT_MAR, 0)) AS BGT_AMT03,
    SUM(ISNULL(KMM101.KMM101_BGT_APR, 0)) AS BGT_AMT04,
    SUM(ISNULL(KMM101.KMM101_BGT_MAY, 0)) AS BGT_AMT05,
    SUM(ISNULL(KMM101.KMM101_BGT_JUN, 0)) AS BGT_AMT06,
    SUM(ISNULL(KMM101.KMM101_BGT_JUL, 0)) AS BGT_AMT07,
    SUM(ISNULL(KMM101.KMM101_BGT_AUG, 0)) AS BGT_AMT08,
    SUM(ISNULL(KMM101.KMM101_BGT_SEP, 0)) AS BGT_AMT09,
    SUM(ISNULL(KMM101.KMM101_BGT_OCT, 0)) AS BGT_AMT10,
    SUM(ISNULL(KMM101.KMM101_BGT_NOV, 0)) AS BGT_AMT11,
    SUM(ISNULL(KMM101.KMM101_BGT_DEC, 0)) AS BGT_AMT12,

    (SUM(ISNULL(KMM101.KMM101_BGT_JAN, 0)) + SUM(ISNULL(KMM101.KMM101_BGT_FEB, 0)) +
     SUM(ISNULL(KMM101.KMM101_BGT_MAR, 0)) + SUM(ISNULL(KMM101.KMM101_BGT_APR, 0)) +
     SUM(ISNULL(KMM101.KMM101_BGT_MAY, 0)) + SUM(ISNULL(KMM101.KMM101_BGT_JUN, 0)) +
     SUM(ISNULL(KMM101.KMM101_BGT_JUL, 0)) + SUM(ISNULL(KMM101.KMM101_BGT_AUG, 0)) +
     SUM(ISNULL(KMM101.KMM101_BGT_SEP, 0)) + SUM(ISNULL(KMM101.KMM101_BGT_OCT, 0)) +
     SUM(ISNULL(KMM101.KMM101_BGT_NOV, 0)) + SUM(ISNULL(KMM101.KMM101_BGT_DEC, 0))
    ) AS AMT01
FROM KMM102
JOIN KMM101 ON KMM101.??? = KMM102.???  -- 原文未明確 JOIN 條件，實務需補齊
GROUP BY
    SUBSTRING(KMM102.KMM102_BGT_TYPE, 1, 4),
    SUBSTRING(KMM102.KMM102_BGT_TYPE, 3, 2),
    SUBSTRING(KMM102.KMM102_BGT_TYPE, 1, 2)

語法特色盤點

• 字串操作：SUBSTRING, CHARINDEX, ISNULL
• 條件邏輯：CASE ... WHEN ... THEN ... ELSE ... END
• 子查詢：標量子查詢取得預算名稱
• 彙總函數：12 個月份 SUM + 總計
• 分組：多層 GROUP BY 對應選取清單
• 相關子查詢：外部欄位關聯內部查詢

優化建議

1. 將 12 個月份欄位常態化（UNPIVOT）為 (Month, Amount)，簡化查詢並利於索引
2. 子查詢改為 LEFT JOIN 可讓最佳化器選擇 Hash/Loop Join
3. 對 KMM102_BGT_TYPE 建立計算欄位索引或持久化計算欄位