SQL Server CASE 表達式進階用法：不只能在 SELECT 列表，還能用在 WHERE、GROUP BY、ORDER BY

基本語法回顧

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

經典用法：SELECT 列表中衍生欄位

USE pubs
GO
SELECT title,
    'Price Range' = CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 AND 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END
FROM titles
ORDER BY price

進階用法一：GROUP BY 分組依據

SELECT 'Number of Titles', COUNT(*)
FROM titles
GROUP BY CASE
    WHEN price IS NULL THEN 'Unpriced'
    WHEN price < 10 THEN 'Bargain'
    WHEN price BETWEEN 10 AND 20 THEN 'Average'
    ELSE 'Gift to impress relatives'
END

關鍵點： GROUP BY 子句中必須完整重複 SELECT 中的 CASE 表達式（或使用相同別名/序數，視版本而定）。

進階用法二：ORDER BY 排序依據

SELECT 
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 AND 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END AS PriceRange,
    title
FROM titles
GROUP BY 
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 AND 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END,
    title
ORDER BY 
    CASE
        WHEN price IS NULL THEN 'Unpriced'
        WHEN price < 10 THEN 'Bargain'
        WHEN price BETWEEN 10 AND 20 THEN 'Average'
        ELSE 'Gift to impress relatives'
    END,
    title

進階用法三：WHERE 子句條件判斷

-- 範例：價格區間篩選
SELECT * FROM titles
WHERE CASE
    WHEN price IS NULL THEN 0
    WHEN price < 10 THEN 1
    WHEN price BETWEEN 10 AND 20 THEN 2
    ELSE 3
END IN (1, 2)  -- 只取 Bargain 和 Average

進階用法四：聚合函數內部條件統計

SELECT 
    COUNT(CASE WHEN price < 10 THEN 1 END) AS bargain_count,
    COUNT(CASE WHEN price BETWEEN 10 AND 20 THEN 1 END) AS average_count,
    COUNT(CASE WHEN price > 20 THEN 1 END) AS premium_count
FROM titles

最佳實踐建議

• 複雜 CASE 邏輯建議封裝為使用者定義函數或檢視表，避免重複維護。
• GROUP BY / ORDER BY 重複 CASE 時，可使用 CROSS APPLY (SQL 2005+) 或 CTE 簡化。
• CASE 會依序評估 WHEN 條件，順序影響效能與結果，將高選擇性條件前置。
• 資料型別優先級：所有 THEN/ELSE 結果會隱含轉換為最高優先級型別，注意隱含轉換陷阱。