SQL进阶_1

CASE 表达式

CASE 表达式有简单 CASE 表达式和搜索 CASE 表达式两种

简单CASE表达式

-- 简单 CASE 表达式
CASE sex
 WHEN '1' THEN '男'
 WHEN '2' THEN '女'
ELSE '其他' END

搜索CASE表达式

-- 搜索 CASE 表达式
CASE WHEN sex = '1' THEN '男'
 WHEN sex = '2' THEN '女'
ELSE '其他' END

我们在编写 SQL 语句的时候需要注意，在发现为真的 WHEN 子句时，

CASE 表达式的真假值判断就会中止，而剩余的 WHEN 子句会被忽略。

注意事项

注意事项 1：统一各分支返回的数据类型

注意事项 2：不要忘了写 END

使用 CASE 表达式的时候，最容易出现的语法错误是忘记写 END。虽

然忘记写时程序会返回比较容易理解的错误消息，不算多么致命的错误。

但是，感觉自己写得没问题，而执行时却出错的情况大多是由这个原因引

起的，所以请一定注意一下。

注意事项 3：养成写 ELSE 子句的习惯

与 END 不同，ELSE 子句是可选的，不写也不会出错。不写 ELSE 子句时，

CASE 表达式的执行结果是 NULL。但是不写可能会造成“语法没有错误，结

果却不对”这种不易追查原因的麻烦，所以最好明确地写上 ELSE 子句（即便

是在结果可以为 NULL 的情况下）。养成这样的习惯后，我们从代码上就可以

清楚地看到这种条件下会生成 NULL，而且将来代码有修改时也能减少失误。

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

需求1:按照省份统计每省的人口 

SQL

-- 按照省份进行人口统计
select 
	case 
		when pref_name = '上海' then '上海市'
		when pref_name = '北京' then '北京市'
		when pref_name = '南京' then '江苏省'
		when pref_name = '常州' then '江苏省'
		when pref_name = '广州' then '广东省'
		when pref_name = '杭州' then '浙江省'
		when pref_name = '苏州' then '江苏省'
		when pref_name = '西安' then '陕西省'
		when pref_name = '郑州' then '河南省'
	else
		pref_name = '其他'
	end
	,
	sum(population)
from
	PopTbl
group by 
	case 
		when pref_name = '上海' then '上海市'
		when pref_name = '北京' then '北京市'
		when pref_name = '南京' then '江苏省'
		when pref_name = '常州' then '江苏省'
		when pref_name = '广州' then '广东省'
		when pref_name = '杭州' then '浙江省'
		when pref_name = '苏州' then '江苏省'
		when pref_name = '西安' then '陕西省'
		when pref_name = '郑州' then '河南省'
	else
		pref_name = '其他'
	end

统计结果

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

 需求2:按照男女性别统计人口

方式1:

select
  pref_name,
	sum(case when sex = '1' then population else 0 end) as man,  -- 如果是男性，就显示人数，如果是女性则显示0
	sum(case when sex = '2' then population else 0 end) as woman-- 如果是女性，就显示人数，如果是男性则显示0
from 
	PopTbl2
group by 
	pref_name;

思路：

select 后查询字段，case 表达式中，如果这个列的值为1，则显示人口信息，不为1，则显示0，下面也同理，查询结果后按照城市名进行分组，分组后求和，便可实现需求。

方式2:

select 
  distinct pt2.pref_name,
	(select population from PopTbl2 pt1 where pt1.pref_name = pt2.pref_name and sex = '1') man,
	(select population from PopTbl2 pt1 where pt1.pref_name = pt2.pref_name and sex = '2') woman
from
	PopTbl2 pt2;

3、在 UPDATE 语句里进行条件分支

 需求3:加减工资

原始数据

-- 1. 对当前工资为 3 万元以上的员工，降薪 10%。
-- 2. 对当前工资为 2.5 万元以上且不满 2.8 万元的员工，加薪 20%。

update Salaries set salary = (case when salary >= 30000 
                                   then salary * 0.9
								   when salary >= 25000 and salary < 28000 
                                   then salary * 1.2
						      else salary
							  end)

效果

3、表之间的数据匹配

需求4:

 实现sql

select 
	course_name,
	case when exists (select 1 from OpenCourses oc where month = 200706 and oc.course_id = cm.course_id) then 'o' else 'X' end as '六月',
	case when exists (select 1 from OpenCourses oc where month = 200707 and oc.course_id = cm.course_id) then 'o' else 'X' end as '七月',
	case when exists (select 1 from OpenCourses oc where month = 200708 and oc.course_id = cm.course_id) then 'o' else 'X' end as '八月'
from 
	CourseMaster cm;

效果

 4、在 CASE 表达式中使用聚合函数

需求5:

1. 获取只加入了一个社团的学生的社团 ID。

2. 获取加入了多个社团的学生的主社团 ID。

方式1:

-- 1. 获取只加入了一个社团的学生的社团 ID。
select std_id,max(club_id) from StudentClub group by std_id having count(1) = 1
union
-- 2. 获取加入了多个社团的学生的主社团 ID。
select std_id,club_id from StudentClub where main_club_flg = 'Y'

方式2：

select 
		std_id,
		case when count(1) = 1 then 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;

 要点！！！

1. 在 GROUP BY 子句里使用 CASE 表达式，可以灵活地选择作为聚合

的单位的编号或等级。这一点在进行非定制化统计时能发挥巨大的威力。

2. 在聚合函数中使用 CASE 表达式，可以轻松地将行结构的数据转换

成列结构的数据。

3. 相反，聚合函数也可以嵌套进 CASE 表达式里使用。

4. 相比依赖于具体数据库的函数，CASE 表达式有更强大的表达能力

和更好的可移植性。

练习

 1、多列数据的最大值

表中数据

实现效果 

 sql

select 
	key1,
	(case when x > y then x
		  when y > z then y
		else z
	 end) as greatest
from 
	Greatests;

 2、转换行列 

表数据

 实现效果

sql

# case ==> group by ==> sum
select
	(case sex when 1 then '男' else '女' end) as 性别,
	sum(population) 全国,
	sum(case when pref_name = '上海' then population else 0 end) as 上海,
	sum(case when pref_name = '苏州' then population else 0 end) as 苏州,
	sum(case when pref_name = '常州' then population else 0 end) as 常州,
	sum(case when pref_name = '南京' then population else 0 end) as 南京,
	sum(case when pref_name = '上海' then population
			 when pref_name = '苏州' then population
			 when pref_name = '常州' then population
			 when pref_name = '南京' then population
		else 0 
		end) as '南方'		 
from 
	PopTbl2
group by 
	sex;

 3、用 ORDER BY 生成“排序”列

需求，按照B-A-D-C 进行排序，效果

​​​​​​​

 实现sql

select 
	key1,
	(case when x > y then x
			 when y > z then y
			 else z
	end) as greatest 
from 
	Greatests
order by
	(case when key1 = 'B' then 1
		  when key1 = 'A' then 2
		  when key1 = 'D' then 3
		  when key1 = 'C' then 4
	 else key1
	 end);