SQL中的CASE WHEN之分类聚合——MYSQL

今天有个SQL查询需求，我第一个就想到用分类聚合查，不过写半天都搞不出来，最后只好全部查出来后用PHP来进行统计。突然发现自己一直以来对CASE WHEN都不是很懂，于是搞完需求后去学习了一下。

原来CASE有两种写法，
一种是简单CASE，CASE `列名` WHEN '值1' THEN ...  WHEN '值2' THEN ... END
一种是搜索CASE，CASE WHEN '判断1' THEN ... WHEN '判断2' THEN ... END

 

例如以下这个表格，想要每个id一行，统计game、work、eat的次数，应该怎么写呢？

注：Mysql版本-5.6.39，表格名-t1

id	type	num
1	game	2
1	work	4
1	game	3
1	eat	1
2	game	2
2	work	9
2	eat	3

方法一（简单CASE）：

SELECT  
    `id`,
    sum(CASE `type` WHEN 'game' THEN `num` ELSE 0 END) AS `sum_game`,
    sum(CASE `type` WHEN 'work' THEN `num` ELSE 0 END) AS `sum_work`, 
    sum(CASE `type` WHEN 'eat' THEN `num` ELSE 0 END) AS `sum_eat`
FROM `t1`
GROUP BY `id`

方法二（搜索CASE）：

SELECT  
    `id`,
    sum(CASE WHEN `type`='game' THEN `num` ELSE 0 END) AS `sum_game`,
    sum(CASE WHEN `type`='work' THEN `num` ELSE 0 END) AS `sum_work`, 
    sum(CASE WHEN `type`='eat' THEN `num` ELSE 0 END) AS `sum_eat`
FROM `t1`
GROUP BY `id`

查询结果如下

id	sum_game	sum_work	sum_eat
1	5	4	1
2	2	9	3

可以注意到简单CASE有点类似switch，对一列进行判断，用多个 WHEN ... THEN 表示不同的分支，用ELSE和END表示最后的default。

而搜索CASE有点类似if...else，用 WHEN...THEN... 表示多个else if，

 

分类聚合查询搞出来了，但是原理还不是很清楚，于是把聚合函数去掉

SELECT  
    `id`,
    (CASE WHEN `type`='game' THEN `num` ELSE 0 END) as `game`,
    (CASE WHEN `type`='work' THEN `num` ELSE 0 END) as `work`, 
    (CASE WHEN `type`='eat' THEN `num` ELSE 0 END) as `num`
FROM `t1`

结果如下

 

id	game	work	num
1	2	0	0
1	0	4	0
1	3	0	0
1	0	0	1
2	2	0	0
2	0	9	0
2	0	0	3

 

可以看到实际上是把type列，扩展成了三列，然后根据type的值，用num填充对应列，

最后加上GROUP BY 和聚合函数，就可以达到分类聚合的目的