這是一段來自預算管理系統的報表查詢,展示了 T-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
CASE 將特定代碼映射為分類代號。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 處理找不到時回傳空字串。SUM(ISNULL(KMM101.KMM101_BGT_JAN, 0)) AS BGT_AMT01,
...
SUM(ISNULL(KMM101.KMM101_BGT_DEC, 0)) AS BGT_AMT12
ISNULL 避免 NULL 導致彙總失敗。(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 計算。
-- 核撥金額:核撥日期 < 參考日期
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
KMM101/KMM102。CONVERT(CHAR(8), date, 112) 字串比較效率低,SQL 2008+ 直接 CAST(date_col AS DATE) < @RefDate。KMM103 相關子查詢可改為 LEFT JOIN + 條件聚合,減少巢狀迴圈。@RefDate CHAR(8) 取代硬編碼,利於計畫重用。KMM102(CHK_DATE, OVER_DATE)、KMM103(YY, BGT_KIND, CASE_NO, PAY_DATE) 覆蓋查詢條件。此查詢雖寫法陳舊,但完整展示了企業級報表常見模式:代碼解碼、主從表關聯、時間點切片、狀態機判斷、多維度彙總。適合作為 T-SQL 進階寫法與重構練習教材。