sql的 case语句

在SQL的世界中CASE语句相当于if -----else  语句。

Case具有两种格式。简单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
这两种方式，可以实现相同的功能。

1、举例：查询发生时间是9月份的各时间段的数量

SELECT
COUNT(resum.id) AS number,
( CASE WHEN DATE_FORMAT(resum.happenedTime,'%H') >'8' AND DATE_FORMAT(resum.happenedTime,'%H') <= '16' THEN '8-16组'
WHEN DATE_FORMAT(resum.happenedTime,'%H') > '16' AND DATE_FORMAT(resum.happenedTime,'%H') <= '24' THEN '16-0组'
WHEN DATE_FORMAT(resum.happenedTime,'%H') > '0' AND DATE_FORMAT(resum.happenedTime,'%H') <= '8' THEN '0-8组'
END) AS projectBreakdown
FROM `naers_eventsresume` resum
INNER JOIN sys_hospital hos ON resum.hospitalId=hos.hospital_id AND hos.status=0
WHERE resum.STATUS=3 AND eventType=1
AND (resum.happenedTime BETWEEN '2018-09-01' AND CONCAT('2018-09-30',' 23:59:59'))
GROUP BY projectBreakdown;

结果如下：

 

 2、查询9月份的事件等级I,II,III,IV的数量

SELECT eventlevel,COUNT(id) FROM `naers_events_ddzc`
WHERE `status`='3'
AND `happenedTime`>='2018-09-01 00:00:00'
AND `happenedTime`<='2018-09-30 23:59:59'
AND `type`='1'
GROUP BY eventlevel;