SQL Server CASE 運算式進階用法全解析：SELECT、GROUP BY、ORDER BY 多場景實戰

基本語法回顧

CASE
    WHEN 條件1 THEN 結果1
    WHEN 條件2 THEN 結果2
    ...
    ELSE 預設結果
END

經典場景：動態產生欄位值

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
GO

進階用法一：GROUP BY 子句中使用 CASE

依價格區間分組統計書籍數量：

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
GO

關鍵規則：GROUP BY 區塊必須完整重複 SELECT 區塊的 CASE 運算式，否則會報錯。

進階用法二：結合 GROUP BY 與 ORDER BY

USE pubs
GO
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 Range,
    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
GO

更多實戰場景

效能與最佳實務

• CASE 運算式在查詢優化器中通常可正常展開，不會顯著影響執行計畫
• 避免在大型資料集上對非索引欄位使用複雜 CASE 導致掃描
• 可搭配 IIF (SQL 2012+) 或 CHOOSE 簡化簡單二元/多選邏輯
• 複雜邏輯建議封裝為使用者定義函數 (UDF) 或預存程序提升可維護性

結語

CASE 遠不止「建立衍生欄位」的工具，善用其在 GROUP BY、ORDER BY、WHERE、聚合等子句中的表達力，能解決許多看似需要動態 SQL 或多次掃描才能完成的分組排序與條件統計問題，是 T-SQL 開發者必備的進階技能。