萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> SQL Server 教學

複雜 SQL 查詢範例解析:單一陳述式涵蓋 CASE、子查詢、轉換、聚合與條件加總

原始 SQL 片段背景

這是一段來自預算管理系統的報表查詢,展示了 T-SQL 進階功能的綜合運用:條件邏輯、字串剖析、多表關聯、條件加總、日期轉換比較。

核心技術點拆解

1. 字串剖析與條件分類 (SUBSTRING + CASE)

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
  • 從預算類型代碼中擷取前 4 碼、第 3-4 碼、前 2 碼分別代表類型、分類、團隊。
  • CASE 將特定代碼映射為分類代號。

2. 相關子查詢取得預算名稱

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
  • 在代碼表 KMC101 中查找對應名稱,用 CHARINDEX 定位分隔符 '-' 後取 12 字元。
  • ISNULL 處理找不到時回傳空字串。

3. 月度預算金額彙總 (12 欄位)

SUM(ISNULL(KMM101.KMM101_BGT_JAN, 0)) AS BGT_AMT01,
...
SUM(ISNULL(KMM101.KMM101_BGT_DEC, 0)) AS BGT_AMT12
  • 逐月加總,ISNULL 避免 NULL 導致彙總失敗。

4. 全年預算總額 (重複加總 vs 計算欄)

(SUM(ISNULL(KMM101.KMM101_BGT_JAN,0)) + ... + SUM(ISNULL(KMM101.KMM101_BGT_DEC,0))) AS AMT01

⚠️ 重複計算 12 次 SUM,效能較差。建議改用 SUM(ISNULL(BGT_JAN,0)+...+ISNULL(BGT_DEC,0)) 或外層 CTE 計算。

5. 條件加總:核撥、支付、超支金額 (CASE + 日期轉換 + 子查詢)

-- 核撥金額:核撥日期 < 參考日期
SUM(ISNULL(CASE WHEN CONVERT(CHAR(8), KMM102.KMM102_CHK_DATE, 112) < 'YYYYMMDD' 
                THEN KMM102.KMM102_CHK_AMT ELSE 0 END, 0)) AS AMT09

-- 超支金額:超支日期 < 參考日期 且 狀態 <> '00'
SUM(ISNULL(CASE 
    WHEN CONVERT(CHAR(8), KMM102.KMM102_OVER_DATE, 112) < 'YYYYMMDD' 
         AND KMM102.KMM102_OVER_STATUS <> '00' 
    THEN KMM102.KMM102_OVER_AMT
    WHEN CONVERT(CHAR(8), KMM102.KMM102_OVER_DATE, 112) < 'YYYYMMDD' 
         AND KMM102.KMM102_OVER_STATUS = '00' 
    THEN KMM102.KMM102_OVER_AMT ELSE 0 END, 0)) AS AMT07
-- 註:兩個 WHEN 條件互斥但結果相同,邏輯可簡化

-- 支付金額:關聯 KMM103 明細表,條件含日期與狀態
SUM(ISNULL((
    SELECT CASE 
        WHEN CONVERT(CHAR(8), KMM103.KMM103_PAY_DATE, 112) < 'YYYYMMDD' 
             AND KMM103.KMM103_PAY_STATUS <> '2' 
        THEN KMM103.KMM103_PAY_AMT ELSE 0 END
    FROM KMM103
    WHERE KMM103.KMM103_YY = KMM102.KMM102_YY
      AND KMM103.KMM103_BGT_KIND = KMM102.KMM102_BGT_KIND
      AND KMM103.KMM103_CASE_NO = KMM102.KMM102_CASE_NO
), 0)) AS AMT03

優化建議

  1. 使用 CTE/衍生表:將月度加總、條件加總邏輯前置計算,避免重複掃描 KMM101/KMM102。
  2. 日期比較改用 DATE 型別:CONVERT(CHAR(8), date, 112) 字串比較效率低,SQL 2008+ 直接 CAST(date_col AS DATE) < @RefDate。
  3. 子查詢改 JOIN:KMM103 相關子查詢可改為 LEFT JOIN + 條件聚合,減少巢狀迴圈。
  4. 參數化 'YYYYMMDD':以變數 @RefDate CHAR(8) 取代硬編碼,利於計畫重用。
  5. 索引建議:KMM102(CHK_DATE, OVER_DATE)、KMM103(YY, BGT_KIND, CASE_NO, PAY_DATE) 覆蓋查詢條件。

學習價值

此查詢雖寫法陳舊,但完整展示了企業級報表常見模式:代碼解碼、主從表關聯、時間點切片、狀態機判斷、多維度彙總。適合作為 T-SQL 進階寫法與重構練習教材。

copyright © 萬盛學電腦網 all rights reserved