CASE 運算式進階應用：SELECT、WHERE、GROUP BY、ORDER BY 全場景實戰

基礎語法

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result_else
END

場景一：SELECT 衍生欄位 (最常見)

USE pubs
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

場景三：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 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

關鍵規則：GROUP BY 區塊必須重複 SELECT 區塊的 CASE 運算式 (或使用序數/別名，視版本而定)。

場景四：WHERE 條件篩選

-- 找出「高價禮品類」書籍
SELECT title, price
FROM titles
WHERE 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 = 'Gift to impress relatives'

場景五：巢狀 CASE 與複合邏輯

SELECT 
    title,
    price,
    'Category' = CASE
        WHEN type = 'business' THEN 
            CASE WHEN price > 20 THEN 'Premium Business' ELSE 'Standard Business' END
        WHEN type = 'psychology' THEN 'Psychology'
        ELSE 'Other'
    END
FROM titles

效能提示

• CASE 運算式在查詢優化器中通常會被展開，複雜 CASE 可能影響統計資料估算
• 大量資料集建議：將 CASE 邏輯移至計算欄位/索引檢視表預先計算
• 避免在 WHERE/ON 子句中對大量資料列使用複雜 CASE，改用 UNION ALL 分拆查詢

總結

CASE 不只是「建立衍生欄位」的工具，更是**在查詢各階段注入條件邏輯**的通用機制。善用它可減少中繼暫存表、簡化應用程式端邏輯、實現動態分組與排序。