?CASE 表達(dá)式是一種通用的條件表達(dá)式，類似于其他編程語言中的if/else語句。

窗口函數(shù)類似于group by，但是不會(huì)改變記錄行數(shù)，能掃描所有行，能對(duì)每一行執(zhí)行聚合計(jì)算或其他復(fù)雜計(jì)算，并把結(jié)果填到每一行中。

1 CASE 表達(dá)式

CASE 表達(dá)式有簡(jiǎn)單CASE表達(dá)式和搜索CASE表達(dá)式兩種寫法：

-- 簡(jiǎn)單 CASE 表達(dá)式
CASE `status`WHEN 1 THEN '正常'WHEN 0 THEN '審核中'ELSE '鎖定'
END-- 搜索CASE表達(dá)式
CASE WHEN `status` = 1 THEN '正常'WHEN `status` = 0 THEN '審核中'ELSE '鎖定'
END ?

??1.1 在SELECT 與 GROUP BY 中同時(shí)使用

圖 城市人口信息表t_city_info

需求：根據(jù)上表，統(tǒng)計(jì)對(duì)應(yīng)省份的人口數(shù)。

圖 統(tǒng)計(jì)出的對(duì)應(yīng)人口數(shù)

SELECT
CASE city 
WHEN '九江' THEN '江西'
WHEN '贛州' THEN '江西' 
WHEN '南昌' THEN '江西'
WHEN '深圳' THEN '廣東'
WHEN '廣州' THEN '廣東'
WHEN '韶關(guān)' THEN '廣東'
WHEN '惠州' THEN '廣東'
ELSE '其他' 
END AS '省份',
SUM(population) AS '人口'
FROM t_city_info
GROUP BY
(
CASE city 
WHEN '九江' THEN '江西'
WHEN '贛州' THEN '江西' 
WHEN '南昌' THEN '江西'
WHEN '深圳' THEN '廣東'
WHEN '廣州' THEN '廣東'
WHEN '韶關(guān)' THEN '廣東'
WHEN '惠州' THEN '廣東'
ELSE '其他' 
END 
)

1.2 在聚合函數(shù)內(nèi)使用CASE表達(dá)式

圖 表城市男女人口數(shù)量表t_city_info,1 表示男性 0 表示女性

?需求，根據(jù)上表統(tǒng)計(jì)出各市男女?dāng)?shù)量，輸出格式如下：

圖 各市男女?dāng)?shù)量

SELECT city as '城市',
SUM(
CASE sex 
WHEN 1 THEN population
ELSE 0
END
) AS '男性',
SUM(
CASE sex 
WHEN 0 THEN population
ELSE 0
END
) AS '女性'
FROM t_city_info
GROUP BY city

1.3 在update里使用CASE

圖 員工薪資信息表t_emplpyee及薪資調(diào)整

需求：工資25000以上的降薪10%，10000以下的漲薪2000。

UPDATE t_employee 
SET salary = 
CASE WHEN salary > 25000 THEN salary * 0.9WHEN salary < 10000 THEN salary + 2000ELSE salary 
END

1.4 在CASE里使用嵌套子查詢

圖 課程信息表t_course_info 與 開課情況t_course_open表

需求：統(tǒng)計(jì)各課程每月開課情況。

圖 各課程每月開課情況

SELECT 
`name` AS '課程',
(CASE WHEN EXISTS  (SELECT `course_id`FROM t_course_openWHERE `month` = '202408'	AND course_id = id ) THEN 'YES'ELSE 'no'END	 
) AS '8月',
(CASE WHEN EXISTS(SELECT `course_id`FROM t_course_openWHERE `month` = '202409'	AND course_id = id ) THEN 'YES'ELSE 'no'END	 
) AS '9月',
(CASE WHEN EXISTS (SELECT `course_id`FROM t_course_openWHERE `month` = '202410'	AND course_id = id ) THEN 'YES'ELSE 'no'END	 
) AS '10月'
FROM t_course_info

1.5 在CASE中使用聚合函數(shù)

圖 學(xué)生加入俱樂部情況t_student_club 表

學(xué)生加入俱樂部情況：1）1個(gè)學(xué)生可以加入多個(gè)俱樂部；2）如果加入了多個(gè)俱樂部，Y標(biāo)志主俱樂部，只加入一個(gè)俱樂部標(biāo)注N。

需求：1）列出學(xué)生參加的主俱樂部。2）如果學(xué)生只假如一個(gè)俱樂部，則也為主俱樂部。

SELECT student_id AS '學(xué)生',
CASE WHEN COUNT(*) = 1 THEN club_nameELSE MAX(CASE WHEN main_flg = 'Y' THEN club_name ELSE NULL END) 
END AS '主俱樂部'
FROM t_student_club
GROUP BY student_id

2 窗口函數(shù)

窗口函數(shù)和聚合函數(shù)共同點(diǎn)在于它們也是對(duì)一組數(shù)據(jù)進(jìn)行分析。但是窗口函數(shù)不是將一組數(shù)據(jù)匯總為單個(gè)結(jié)果，而是針對(duì)查詢中的每一行數(shù)據(jù)，基于和它相關(guān)的一組數(shù)據(jù)計(jì)算出一個(gè)結(jié)果。語法如下：

window_fun (expr) over (

??? ?partition by …

order by …

frame_clause

)

PARTITION BY 子句分隔記錄集合，類似于group by

ORDER BY 子句對(duì)記錄排序

frame_clause 串口大小，幀子句，定義以當(dāng)前記錄為中心的子集。

2.1 匿名窗口與命名窗口

圖 服務(wù)器每日請(qǐng)求量t_service_load 表

需求：列出服務(wù)器每日請(qǐng)求量、前兩日請(qǐng)求評(píng)價(jià)數(shù)。

SELECT `date`,`load`,
AVG(`load`) OVER (ORDER BY `date`ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
) AS avgLoad
FROM t_service_load;

2.1.1 命名窗口

SELECT `date`,`load`,
AVG(`load`) OVER loadW AS avgLoad,
SUM(`load`) OVER loadW AS sumLoad
FROM t_service_loadWINDOW loadW AS (ORDER BY `date`ROWS BETWEEN 2 PRECEDING AND 1 PRECEDING
)

2.2 幀子句

ROWS	按行設(shè)置移動(dòng)單位。
RANGE	按列值設(shè)置移動(dòng)單位。使用ORDER BY 子句來指定基準(zhǔn)列。
n PRECEDING	僅向前（行號(hào)較小的方向）移動(dòng)n行。
n FOLLOWING	僅向后移動(dòng)n行。
UNBOUNDED PRECEDING	一直移動(dòng)到最前面。
UNBOUNDED FOLLOWING	一直移動(dòng)到最后面。
CURRENT ROW	當(dāng)前行。

表 幀子句中可以使用的選項(xiàng)

需求：統(tǒng)計(jì)上表中，兩日前的請(qǐng)求量。

SELECT `date`,`load`,
MAX(`load`) OVER (ORDER BY `date` ASC RANGE BETWEEN INTERVAL 2 DAY PRECEDING AND INTERVAL 2 DAY PRECEDING
) AS preLoad
FROM t_service_load;