SQL 中 CASE 表示式的使用方式

2022-09-20 22:04:00

1. 前言

CASE 表示式是從 SQL-92 標準開始被引入的。

在 CASE 表示式裡，可以使用 BETWEEN 、LIKE和 < 、> 等便利的謂詞組合，以及能巢狀子查詢的 IN 和 EXISTS 謂詞。

2. 語法

CASE 表示式有簡單 CASE 表示式（simple case expression）和搜尋 CASE 表示式（searched case expression） 兩種寫法：

-- 簡單CASE 表示式
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他'END
-- 搜尋CASE 表示式
CASE WHEN sex = '1' THEN '男'
     WHEN sex = '2' THEN '女'
ELSE '其他' END

sex 列（欄位）如果是 '1' ，那麼結果為男；如果是 '2' ，那麼結果為女。

3. 注意點

CASE在匹配給定條件時，發現為真的 WHEN 子句時，CASE 表示式的真假值判斷就會中止，而剩餘的 WHEN 子句會被忽略。為了避免引起不必要的混亂，使用 WHEN 子句時要注意條件的排他性。

-- 例如，這樣寫的話，結果裡不會出現「第二」
CASE WHEN col_1 IN ('a', 'b') THEN '第一'
     WHEN col_1 IN ('a') THEN '第二'
ELSE '其他' END

統一各分支返回的資料型別： 一定要注意 CASE 表示式裡各個分支返回的資料型別是否一致。某個分支返回字元型，而其他分支返回數值型的寫法是不正確的。
不要忘了寫 END： 不寫END是語法錯誤，這是不允許的。
養成寫 ELSE 子句的習慣： 與 END 不同，ELSE 子句是可選的，不寫也不會出錯。不寫 ELSE 子句時，CASE 表示式的執行結果是 NULL 。

4. 分類彙總資料

SELECT 
    CASE pref_name 
        WHEN '德島' THEN '四國'
        WHEN '香川' THEN '四國'
        WHEN '愛媛' THEN '四國'
        WHEN '高知' THEN '四國'
        WHEN '福岡' THEN '九州'
        WHEN '佐賀' THEN '九州'
        WHEN '長崎' THEN '九州'
    END AS district,
    SUM(population) AS total
FROM poptbl
GROUP BY 
    CASE pref_name 
        WHEN '德島' THEN '四國'
        WHEN '香川' THEN '四國'
        WHEN '愛媛' THEN '四國'
        WHEN '高知' THEN '四國'
        WHEN '福岡' THEN '九州'
        WHEN '佐賀' THEN '九州'
        WHEN '長崎' THEN '九州'
    END;

5. 一條SQL實現不同條件的統計

SELECT
    pref_name AS '縣名',
    SUM( CASE WHEN sex=1 THEN population ELSE 0 END ) AS '男' 
    SUM( CASE WHEN sex=2 THEN population ELSE 0 END ) AS '女' 
FROM poptlb
GROUP By pref_name

6. 使用CHECK約束定義多個列的條件關係

假設某公司規定“女性員工的工資必須在 20 萬日元以下”，而在這個公司的人事表中，這條無理的規定是使用 CHECK 約束來描述的，程式碼如下所示：

CONSTRAINT check_salary CHECK ( 
    CASE WHEN sex = '2' THEN 
        CASE WHEN salary <= 200000
            THEN 1 ELSE 0 END
        ELSE 1 END = 1 
)

7. 在UPDATE語句中進行條件分支

條件：

對當前工資為 30 萬日元以上的員工，降薪 10%。
對當前工資為 25 萬日元以上且不滿 28 萬日元的員工，加薪 20%。

UPDATE Salaries
SET salary = CASE WHEN salary>300000 THEN salary*0.9
                  WHEN salary>=250000 AND salary <280000 THEN salary * 1.2
                  ELSE salary
             END;

8. 生成交叉表

--- 使用IN謂詞
SELECT
    course_name AS '課程名',
    CASE WHEN courese_id IN (SELECT course_id FROM open_course WHERE mouth = '200706')
            THEN 'o' 
            ELSE 'x' 
    END AS '6 月'
    CASE WHEN courese_id IN (SELECT course_id FROM open_course WHERE mouth = '200707')
            THEN 'o' 
            ELSE 'x' 
    END AS '7 月'
    CASE WHEN courese_id IN (SELECT course_id FROM open_course WHERE mouth = '200708')
            THEN 'o' 
            ELSE 'x' 
    END AS '8 月'
FROM course_master;

--- 或者使用EXIST謂詞
SELECT CM.course_name,
    CASE WHEN EXISTS (SELECT course_id FROM OpenCourses OC WHERE month = 200706 AND OC.course_id = CM.course_id) 
        THEN '○' ELSE '×' 
    END AS "6 月",
    CASE WHEN EXISTS (SELECT course_id FROM OpenCourses OC WHERE month = 200707 AND OC.course_id = CM.course_id) 
        THEN '○' ELSE '×' 
    END AS "7 月",
    CASE WHEN EXISTS (SELECT course_id FROM OpenCourses OC WHERE month = 200708 AND OC.course_id = CM.course_id) 
        THEN '○' ELSE '×' 
    END AS "8 月"
FROM CourseMaster CM;

9. CASE表示式中使用聚合函數

對於加入了多個社團的學生，通過將其“主社團標誌”列設定為 Y 或者 N 來表明哪一個社團是他的主社團；對於只加入了一個社團的學生，將其“主社團標誌”列設定為 N。

現需要查詢出所有學生加入的社團，若加入了多個則顯示主社團

SELECT 
    std_id,
    CASE WHEN COUNT(*)==1 THEN MAX(club_id) 
         ELSE MAX(CASE WHEN main_club_flg = 'Y' THEN club_id ELSE NULL END)
    END AS 'main_club'
FROM student_club
GROUP BY std_id

10. 按照自定義規則排序列

按照mark列排序，要求修正a b c d 的權重為 c b a d

SELECT
    mark
FROM 
    sort_test
ORDER BY
    CASE mark
        WHEN 'a' THEN -1
        WHEN 'b' THEN 1
        WHEN 'c' THEN 2
        WHEN 'd' THEN -2
    END

到此這篇關於 SQL 中 CASE 表示式的使用方式的文章就介紹到這了,更多相關 SQL CASE 表示式內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！