SQL Server CASE 運算式進階用法大全

CASE 運算式是 T-SQL 中最靈活的條件邏輯工具，許多開發者僅用於 SELECT 列表產生衍生欄位，實則可用於 WHERE、GROUP BY、ORDER BY、HAVING 等多個子句，甚至構建動態排序與分組邏輯。

語法回顧：兩種形式

簡單 CASE 運算式

CASE input_expression
    WHEN when_expression THEN result_expression
    [...]
    ELSE else_result_expression
END

搜尋 CASE 運算式 (較通用)

CASE
    WHEN Boolean_expression THEN result_expression
    [...]
    ELSE else_result_expression
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
GO

進階用法 1：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

進階用法 2：結合 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

重要規則：GROUP BY 子句中必須重複 SELECT 列表中的完整 CASE 運算式，不能只用別名或欄位序數。

進階用法 3：WHERE 子句動態篩選

-- 依參數決定篩選邏輯
DECLARE @FilterType VARCHAR(20) = 'PriceRange'  -- 或 'Category', 'Author'

SELECT *
FROM titles
WHERE 1 = CASE
    WHEN @FilterType = 'PriceRange' AND price BETWEEN 10 AND 20 THEN 1
    WHEN @FilterType = 'Category' AND type = 'popular_comp' THEN 1
    WHEN @FilterType = 'Author' AND EXISTS (SELECT 1 FROM titleauthor ta WHERE ta.title_id = titles.title_id AND ta.au_id = '123-45-6789') THEN 1
    ELSE 0
END

進階用法 4：動態排序 (ORDER BY CASE)

DECLARE @SortOrder VARCHAR(10) = 'PriceDesc'  -- 'PriceAsc', 'TitleAsc', 'DateDesc'

SELECT title, price, pubdate
FROM titles
ORDER BY CASE
    WHEN @SortOrder = 'PriceAsc' THEN price
    WHEN @SortOrder = 'PriceDesc' THEN -price
    WHEN @SortOrder = 'TitleAsc' THEN title
    WHEN @SortOrder = 'DateDesc' THEN -CAST(pubdate AS FLOAT)
    ELSE 0
END

進階用法 5：巢狀 CASE 與複合條件

SELECT
    title,
    PriceCategory = CASE
        WHEN price IS NULL THEN 'Unknown'
        WHEN price < 10 THEN 'Low'
        WHEN price < 20 THEN CASE
            WHEN type = 'popular_comp' THEN 'Mid-Tech'
            ELSE 'Mid-General'
        END
        ELSE 'High'
    END
FROM titles

效能考量與最佳實務

CTE 重構範例：避免重複 CASE

WITH PriceBands AS (
    SELECT
        title_id,
        title,
        price,
        PriceBand = CASE
            WHEN price IS NULL THEN 'Unpriced'
            WHEN price < 10 THEN 'Bargain'
            WHEN price BETWEEN 10 AND 20 THEN 'Average'
            ELSE 'Premium'
        END
    FROM titles
)
SELECT PriceBand, COUNT(*) AS TitleCount
FROM PriceBands
GROUP BY PriceBand
ORDER BY CASE PriceBand 
    WHEN 'Unpriced' THEN 1 
    WHEN 'Bargain' THEN 2 
    WHEN 'Average' THEN 3 
    ELSE 4 END

總結

CASE 運算式將程式邏輯帶入宣告式 SQL，善用可大幅減少應用層往返處理。記住：SELECT 列表、GROUP BY、ORDER BY 可重用同一 CASE；WHERE/HAVING 需謹慎評估效能；複雜邏輯優先用 CTE 拆解。