1-1 case表达式-SQL进阶教程

case表达式概述

case表达式的写法

-- 简单case表达式
case sex
	when '1' then '男'
	when '2' then '女'
	else '其他'
end	

-- 搜索case表达式
case when sex='1' then '男'
	when sex='2' then '女'
 	else '其他'
end

剩余的when子句被忽略的写法示例

-- 例如, 这样写的话, 结果里不会出现 "第二"
case when col_1 in('a','b') then '第一'
	when col_2 in('a') then '第二'
	else '其他'
end

注意事项 1：统一各分支返回的数据类型
注意事项 2：不要忘了写END
注意事项 3：养成写ELSE子句的习惯

将已有编号方式转换为新的方式并统计

-- 把县编号转换成地区编号
select 
case pref_name
	when '德岛' then '四国'
	when '香川' then '四国'
	when '爱媛' then '四国'
	when '高知' then '四国'
	when '福冈' then '九州'
	when '左贺' then '九州'
	when '长崎' then '九州'
	else '其他'
end 
as district,
sum(population)
from PopTbl
group by 
case pref_name
	when '德岛' then '四国'
	when '香川' then '四国'
	when '爱媛' then '四国'
	when '高知' then '四国'
	when '福冈' then '九州'
	when '左贺' then '九州'
	when '长崎' then '九州'
	else '其他'
end;

-- 按人口数量等级划分都道府县
select
case 
	when population<100 then '01'
	when population>=100 and population<200 then '02'
	when population>=200 and population<300 then '03'
	when population>300 then '04'
	else null
end
as pop_class,
count(*) as cnt
from PopTbl
group by
case 
	when population<100 then '01'
	when population>=100 and population<200 then '02'
	when population>=200 and population<300 then '03'
	when population>300 then '04'
	else null
end;

-- 把县编号转换成地区编号: 将case表达式归纳到一处
select 
case pref_name
	when '德岛' then '四国'
	when '香川' then '四国'
	when '爱媛' then '四国'
	when '高知' then '四国'
	when '福冈' then '九州'
	when '左贺' then '九州'
	when '长崎' then '九州'
	else '其他'
end 
as district,
sum(population)
from PopTbl
group by district;	-- group by子句里引用了select子句中定义的别名
/*
严格来说，这种写法是违反标准SQL的规则的。因为GROUP BY子句比SELECT语句先执行
但在PostgreSQL和MySQL中，这个查询语句可以顺利执行
*/

用一条SQL语句进行不同条件的统计

select pref_name,
-- 男性人口
sum(
	case
		when sex='1' then population
		else 0
	end
) as cnt_m,
-- 女性人口
sum(
	case
		when sex='2' then population
		else 0	
	end
) as cnt_f
from PopTbl2
group by pref_name;

用check约束定义多个列的条件关系

-- 蕴含式: 如果是女性员工，则工资是20万日元以下
constraint check_salary check(
	case
		when sex='2' then
			case
				when salary<=200000 then 1
				else 0
			end
		else 1
		end=1
)
/*
要想让蕴含式P→Q为真，需要命题P和命题Q均为真，或者P为假，或者P无法判定真假
如果不满足“是女性”这个前提条件，则无需考虑工资约束
*/

-- 逻辑与: 该公司将不能雇佣男性员工
constraint check_salary check(
	sex='2' and salary<=200000
)
/*
要想让逻辑与P∧Q为真，需要命题P和命题Q均为真，或者一个为真且另一个无法判定真假
能在这家公司工作的是“性别为女且工资在20万日元以下”的员工，以及性别或者工资无法确定的员工
*/

在update语句里进行条件分支

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;	

-- 用case表达式调换主键值
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');

表之间的数据匹配

-- 表的匹配：使用in谓词
select course_name,
case
	when course_id in(
	select course_id
	from OpenCourses
	where month=200706
	)
	then '○'
	else '×'
end
as '6月',
case
	when course_id in(
	select course_id
	from OpenCourses
	where month=200707
	)
	then '○'
	else '×'
end
as '7月',
case
	when course_id in(
	select couse_id
	from OpenCouses
	where month=200708
	)
	then '○'
	else '×'
end
as '8月'
from CourseMaster;

-- 表的匹配：使用exist谓词
select CM.course_id,
case
	when exists(
	select couse_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
	where month=200708
	and OC.course_id=CM.course_id
 )
	then '○'
 	else '×'
end
as '8月'
from CouseMaster CM;
/*
无论使用IN还是EXISTS，得到的结果是一样的，但从性能方面来说，EXISTS更好
通过EXISTS进行的子查询能够用到“month, course_id ”这样的主键索引，因此尤其是当表OpenCourses里数据比较多的时候更有优势
*/

在case表达式中使用聚合函数

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 StudentClub
group by std_id;