SQL——case的用法

case when

case when 用于计算条件列表的表达式，并返回条件对应的结果。通俗一点讲就是“如果……就……”。

语法：

case 
when condition_1 then result_1
when condition_2 then result_2
…
else result_n
end

注意事项：

• 在sql中如果需要进行如“如果……就……”的逻辑判断，最好使用case when；
• 条件大于等于1即可；
• else 根据需求决定，可有可无；
• 如果满足了condition_1，会返回result_1，不会考虑后面的条件，即若后面又满足condition_2，并不会返回result_2，这是逻辑判断的先入为主原则；
• 最后结束语“end”不可省略。

例如：
当 sex=‘1’ 时，性别为‘男’；当 sex=‘0’ 时，性别为‘女’；

case 
when sex='1' then '男'
when sex='0' then '女'
else '其他'		# else 可有可无
end
as '性别';	# 新的列名为‘性别’

当 sex=‘1’ 且 age>‘18’ 时，年龄段为‘成年男性’；
当 sex=‘1’ 且 ‘7’<age<‘18’ 时，年龄段为‘未成年男性’；
当 sex=‘0’ 且 age>‘18’ 时，年龄段为‘成年女性’；
当 sex=‘0’ 且 ‘7’<age<‘18’ 时，年龄段为‘未成年女性’；
当 age<‘7’ 时，年龄段为‘婴幼儿’；

select name,
case 
when sex='1' and age>18 then '成年男性'
when sex='1' and age>7 then '未成年男性' 	# 根据逻辑判断先入为主，省略判断 'age<18'
when sex='0' and age>18 then '成年女性'
when sex='0' and age>7 then '未成年女性'
else '婴幼儿'
end
as '年龄段'	# 新的列名为‘年龄段’
from Preson;

case 行转列

case 用的比较广泛的功能就是行转列，就是将记录行里的数据按条件转换成具体的列。

现有如下例子：
例子来源点击此处（为了自己思考SQL语句，对视频中表数据和参数等做了修改）

已有一个课程详细记录表，现需要通过sql查询整理出一周内每天的课程表。

输入：

id	day	course_name	morning	afternoon
1	周一	语文	有课	有课
2	周一	数学	null	有课
3	周三	语文	有课	null
4	周四	数学	null	有课
5	周四	英语	有课	null
6	周五	英语	有课	null

输出：

time	周一	周二	周三	周四	周五
上午	语文	无课	语文	英语	英语
下午	语文，数学	无课	无课	数学	无课

SQL语句如下：

# 创建表
create table course(
id int,
day varchar(20) not null default '',
course_name varchar(20) not null,
morning varchar(20),
afternoon varchar(20),
primary key('id')
);

# 插入表数据
insert into course values(1,'周一','语文','有课','有课')；
insert into course values(2,'周一','数学',null,'有课')；
insert into course values(3,'周三','语文','有课',null)；
insert into course values(4,'周四','数学',null,'有课')；
insert into course values(5,'周四','英语','有课',null)；
insert into course values(6,'周五','英语','有课',null)；

# 查询
select time,
ifnull(group_concat(case when day='周一' and ifyouke='有课' then course_name else null end),'无课') '周一',
ifnull(group_concat(case when day='周二' and ifyouke='有课' then course_name else null end),'无课') '周二',
ifnull(group_concat(case when day='周三' and ifyouke='有课' then course_name else null end),'无课') '周三',
ifnull(group_concat(case when day='周四' and ifyouke='有课' then course_name else null end),'无课') '周四',
ifnull(group_concat(case when day='周五' and ifyouke='有课' then course_name else null end),'无课') '周五',
from
(
select day,course_name,morning as ifyouke,'上午' as time 
from course
union all
select day,course_name,afternoon as ifyouke,'下午' as time 
from course
) as t
group by time;

查询语句思路解读：

1. 将course表拆分出上午有课情况和下午有课情况，得到 t 表

select day,course_name,morning as ifyouke,'上午' as time 
from course
union all
select day,course_name,afternoon as ifyouke,'下午' as time
from course

上面的sql语句执行结果如下图：

2. 按 “上午” 和 “下午” 分组查询每天的有课情况

首先对 t 表按 “time” 进行分组，通过 case when 按 “day” 和 “ifyouke” 选取有课的记录，但是这样查询的结果如下：

select time,
case when day='周一' and ifyouke='有课' then course_name else null end as '周一'  # as可省略
from t
group by time;

从上图可以看到周一下午有语文和数学，两条记录是分开的，而实际上希望得到的是语文和数学合并在一条记录。

所以接下来需要引入 group_concat 函数，将同一个分组的字段连接起来，返回一个字符串结果。
group_concat用法

select time,
group_concat(case when day='周一' and ifyouke='有课' then course_name else null end) '周一'   
from t
group by time;

最后通过 ifnull 函数将没有课程安排的时间设置为“无课”，例如周三下午没有课程安排，按上述语句会返回结果“null”（如下面左图），加上 ifnull 函数后返回结果如下面右图：

select time,
ifnull(group_concat(case when day='周三' and ifyouke='有课' then course_name else null end),'无课') '周三'
from t
group by time;

3. 经过上述查询就可以得到一天的有课情况，之后就是复制粘贴 ifnull(group_concat(case when…),…） 依次得到每天的有课情况，结果如下：

select time,
ifnull(group_concat(case when day='周一' and ifyouke='有课' then course_name else null end),'无课') '周一',
ifnull(group_concat(case when day='周二' and ifyouke='有课' then course_name else null end),'无课') '周二',
ifnull(group_concat(case when day='周三' and ifyouke='有课' then course_name else null end),'无课') '周三',
ifnull(group_concat(case when day='周四' and ifyouke='有课' then course_name else null end),'无课') '周四',
ifnull(group_concat(case when day='周五' and ifyouke='有课' then course_name else null end),'无课') '周五',
from t
group by time;

行列转置新方法——PIVOT

PIVOT用于将列值旋转为列名（即行转列），在SQL Server 2000可以用聚合函数配合CASE语句实现

PIVOT的一般语法是：PIVOT(聚合函数(列) FOR 列 in (…) )AS P

table_source
PIVOT(
聚合函数（value_column）
FOR pivot_column
IN(<column_list>)
)

UNPIVOT用于将列明转为列值（即列转行），在SQL Server 2000可以用UNION来实现

table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)

关于pivot的详细讲解点击进入