-- 簡(jiǎn)單CASE 表達(dá)式
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他'END
-- 搜索CASE 表達(dá)式
CASE WHEN sex = '1' THEN '男'
     WHEN sex = '2' THEN '女'
ELSE '其他' END

-- 例如，這樣寫的話，結(jié)果里不會(huì)出現(xiàn)“第二”
CASE WHEN col_1 IN ('a', 'b') THEN '第一'
     WHEN col_1 IN ('a') THEN '第二'
ELSE '其他' END

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

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

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

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;

--- 使用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;

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

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