case 表达式

case表达式

case 表达式分为两种

1. 简单case表达式

   case sex
   	when '1' then '男'
   	when '2' then '女'
   else '其他' end
   

2. 搜索 case 表达式

   case when sex='1' then '男'
   	 when sex='2' then '女'
   else '其他' end
   

以上两种表达式是等效的。
在编写 case 表达式时需要注意的是 when 子句具有排他性，如果第一个when子句成立，那么第二个 when 子句即使成立也不会执行。此外，else 子句并不是强制编写的，若不写 else 则返回 null。

case表达式的几种用法

对已有数据按照新的维度进行统计

例如有以下几个数据
```sql
insert into PopTbl values
('德岛',100),
('香川',200),
('爱媛',150),
('高知',200),
('福冈',300),
('佐贺',100),
('长崎',200),
('东京',400),
('群马',50)
```
表中的数据代表的时县级单位的人口数据，如果我们需要统计省级的人口单位，但是表中有没有相应的维度字段，这种时候就可以用case表达式的形式来来进行统计。
```sql
select case pref_name
 when '德岛' then '四国'
 when '香川' then '四国'
 when '爱媛' then '四国'
 when '高知' then '四国'
 when '福冈' then '九州'
 when '佐贺' then '九州'
 when '长崎' then '九州'
 else '其他' end as dis,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;
 ```

结果如下图：

需要注意的是 case 表达式在 select 子句和 group by 子句中均需要写出来，不过对于 MySQL 和 PostgreSQL 来说还有一种写法。

select case pref_name
     when '德岛' then '四国'
     when '香川' then '四国'
     when '爱媛' then '四国'
     when '高知' then '四国'
     when '福冈' then '九州'
     when '佐贺' then '九州'
     when '长崎' then '九州'
     else '其他' end as dis,sum(population)
 from PopTbl group by dis;

可以看到这种写法也能得出正确结果，不过这种写法并不规范，因为 group by 语句在select 语句前执行，因此读不到在 select 子句中定义的别名在 Oracle 和 SQL server 数据库中会报错。而 MySQL 在执行时会相对select子句中的列表进行扫描，并对列进行计算，因此可以使用上述语法。

用一条 sql 进行不同条件的统计（行转列）

现有如下数据

insert into PopTbl2 values
('德岛',1,60),
('德岛',2,40),
('香川',1,100),
('香川',2,100),
('爱媛',1,100),
('爱媛',2,50),
('高知',1,100),
('高知',2,100),
('福冈',1,100),
('福冈',2,100),
('佐贺',1,20),
('佐贺',2,80),
('长崎',1,125),
('长崎',2,125),
('东京',1,250),
('东京',2,150)

现在要统计分地区男女人口情况，结果如下形式

select pref_name,
sum(case when sex=1 then population end)'男',
sum(case when sex=2 then population end)'女'
from PopTbl2
group by pref_name;

此处用 case 表达式可以在一条语句中直接计算出结果，如果不使用 case 表达式则需要分别计算男女人口数再进行合并操作。

在 update 语句中进行条件分支

例如有一张工资表，现规定，对于目前工资超过一万的人员降薪 10%，对目前工资不足九千的员工涨薪 20%。如果直接按照字面逻辑来编写的话，会有如下两条 SQL。

update salaries
	set salary = salary*0.9
where salary>=10000;

update salaries
	set salary = salary*1.2
where salary<=9000;

这样写初看符合逻辑但结果确实错误的，假设张三目前工资是 10000，那么按照逻辑他的工资会先被降到9000，然后再涨到10800，这样的结果显然是不对的。出现问题的原因是进行了多次 update，第一次 update 的结果在第二次 update 时又被计算了一遍，所以为了避免这种情况，最好只使用一次 update，语句如下：

update salaries
set salary = case when salary>=10000
				  then salary*0.9
				  when salary<=9000
				  then salary*1.2
				  else salary end;

这样写就不会再出现重复更新的问题了。

表之间的数据匹配

与 decode 等函数相比，case 表达式的一大优势在于能够判断表达式。也就是说，在 case 表达式中，我们可以使用 between、like 和 >、< 等便利的谓词组合，以及能嵌套子查询的 in 和 exists 谓词。因此，case 表达式具有非常强大的表达力。

create table CourseMaster(
course_id int,
course_name varchar(20)
)
comment "课程一览"
character set=utf8;

insert into CourseMaster 
values(1,"会计入门"),
(2,"财务知识"),
(3,"簿记开始"),
(4,"税务师");

create table OpenCourse(
month varchar(10),
course_id int
)comment "开设的课程";

insert into OpenCourse values
('200706',1),
('200706',3),
('200706',4),
('200707',4),
('200708',2),
('200708',4);

如果需要将数据统计为以下格式，则可以使用 case 表达式。

select course_name,
case when course_id in(select course_id from OpenCourse where month='200706')then '⭕' else 'x' end as '6月',
case when course_id in(select course_id from OpenCourse where month='200707')then '⭕' else 'x' end as '7月',
case when course_id in(select course_id from OpenCourse where month='200708')then '⭕' else 'x' end as '8月'
from CourseMaster;

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

现有如下表,记录的是学生加入俱乐部的情况，学生若加入多个俱乐部则会有主俱乐部标识来标记主俱乐部，对于只加入了一个俱乐部的学生则其主俱乐部标识记为 N。

create table StuentClub(
std_id int,
club_id int,
club_name varchar(50),
main_club_flg varchar(5)
)
character set=utf8;

insert into StuentClub values
(100,1,'棒球','Y'),
(100,2,'管弦乐','N'),
(200,2,'管弦乐','N'),
(200,3,'羽毛球','Y'),
(200,4,'足球','N'),
(300,4,'足球','N'),
(400,5,'游泳','N'),
(500,6,'围棋','N');

现在需要按如下要求统计信息：

1. 获取只加入一家俱乐部学生的俱乐部ID
2. 获取加入多家俱乐部学生的主俱乐部ID
   一般情况下会写两部分 SQL

select std_id,max(club_id)
from StuentClub
group by std_id
having count(1)=1;
select std_id,club_id
from StuentClub
where main_club_flg='Y';

最后将结果 union 起来即可得到需要的数据。用基础写法需要写两段 sql 再将其连接起来，如果使用 case 表达式则一段 sql 就能完成。

select std_id,
	case when count(1)=1 then max(club_id)
	else max(case when main_club_flg='Y' then club_id else null end)
	end as club_id
from StuentClub group by std_id;

在该段 sql 中，我们首先对学生编号进行分组，然后在 case 表达式中对分组后的数据量进行统计，选出只参加了一个社团的学生，有一点需要注意，对于分组后的数据，就算该组只有一条数据，在取出具体的数据时，也需要使用聚合函数。再来看第二段语句，这里我们取出参加了多个社团的学生组别，然后再嵌套一层 case 表达式，来选择其中的主社团信息，也就是说，对于分组后的数据，我们使用 case 表达式是可以对组内的信息进行筛选的，筛选后再使用聚合函数将数据取出来。这里一开始有个疑问，对于第二段的逻辑，我们能不能将 max 函数放到里面，写成else case when main_club_flg='Y' then max(club_id) else null end 这种形式呢，答案是不能，如果我们这么写会报错提示 main_club_flg 不在分组字段中，但如果我们使用的筛选条件 std_id 字段，则可以将 max 写到里面。本质上来说也就是 group by 语句的使用限制：select 字段必须包含在 group by 语句中或者包含在聚合函数中。这样看来将 max 写到外面的话使用范围会更广泛一点。

行列转换（额外实例）

此处使用之前行列转换中用到的表 PopTbl2，现在想要将数据处理成如下所示的格式：

从数据格式可以知道该数据需要以性别字段来分组，然后再对人口数据进行聚合操作，由此可写出如下 sql。

select  case when sex=1 then '男' 
        when sex=2 then '女'
        else null
        end as 性别,
        sum(population) 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 else 0 end) as '高知',
        sum(case when pref_name in('德岛','香川','爱媛','高知') then population else 0 end) as '四国(再揭)'
from PopTbl2 group by sex;

用 order by 进行自定义排序

假定有一张表 order_a,其表结构及数据如下：

create table order_a(
`key` varchar(10),
value int
);
insert into order_a values
('A',2),
('B',6),
('C',3),
('D',7);

默认情况下，如果使用 select * from order_a order by `key`;来进行查询的话，则输出结果会按照 A、B、C、D 的字母顺序来进行排序，但如果我们要按照 BADC 的顺序来进行排序的话普通的写法肯定就不行了，这里就可以使用 case 表达式来完成。

select * from order_a
order by case `key`
when 'B' then 1
when 'C' then 2
when 'D' then 3
when 'A' then 4
else `key`
end

这样就能得到按照自定义顺序排序的数据了。