一、用法
case
when sex = '1' then '男'
when sex = '2' then '女'
else '未知' end
Case函數(shù)只返回第一個(gè)符合條件的值，剩下的Case部分將會(huì)被自動(dòng)忽略。
Case when 相當(dāng)于一個(gè)自定義的數(shù)據(jù)透視表，group by 是行名，case when 負(fù)責(zé)列名。
二、實(shí)例
- 與GROUP BY 結(jié)合，自定義列聯(lián)表統(tǒng)計(jì)
SELECT country,
SUM( CASE WHEN sex = '1' THEN population ELSE 0 END), --男性人口
SUM( CASE WHEN sex = '2' THEN population ELSE 0 END) --女性人口
FROM Table_A GROUP BY country;
-
增加分組條件
增加分組條件.png
- 與Group by結(jié)合，自定義分組統(tǒng)計(jì)
/*新建一個(gè)表demo*/
CREATE TABLE demo (
type varchar(10) NULL,
num int NULL)
/*插入數(shù)據(jù)*/
insert into demo values ('A',10);
insert into demo values ('B',10);
insert into demo values ('C',10);
insert into demo values ('D',10);
insert into demo values ('E',10);
insert into demo values ('F',10);
insert into demo values ('G',10);
/*利用CASE WHEN 和 GROUP BY 實(shí)現(xiàn)自定義分組合計(jì)*/
select
case
when type in('A','C','D') THEN 'ACD'
when type in('F','G') THEN 'FG'
else type end as types,sum(num)
from demo
group by
case
when type in('A','C','D') THEN 'ACD'
when type in('F','G') THEN 'FG'
else type end
-
原始表
原始表.png -
select 結(jié)果
select結(jié)果
- 與distinct結(jié)合，去重分組統(tǒng)計(jì)
select
count(distinct case when photo='1' then id else null end ）photo1_count,
count(distinct case when photo='2' then id else null end ）photo2_count
from photos
4.根據(jù)條件有選擇的UPDATE
UPDATE Personnel
SET salary =
CASE
WHEN salary >= 5000 THEN salary * 0.9
WHEN salary >= 2000 AND salary < 4600 THEN salary * 1.15
ELSE salary END;
UPDATE SomeTable
SET p_key = CASE WHEN p_key = 'a' THEN 'b'
WHEN p_key = 'b' THEN 'a' ELSE p_key END
WHERE p_key IN ('a', 'b');
5.兩個(gè)表數(shù)據(jù)是否一致的檢查
--使用IN的時(shí)候
SELECT keyCol,
CASE WHEN keyCol IN ( SELECT keyCol FROM tbl_B ) THEN 'Matched'
ELSE 'Unmatched' END Label
FROM tbl_A;
--使用EXISTS的時(shí)候
SELECT keyCol,
CASE WHEN EXISTS ( SELECT * FROM tbl_B WHERE tbl_A.keyCol = tbl_B.keyCol ) THEN 'Matched' ELSE 'Unmatched' END Label
FROM tbl_A;
參考：用法詳解
