sql中case的用法

表格：

SQL脚本：

脚本1：

select case city_name
when '深圳' THEN '华南'
when '广州' THEN '华南'
when '佛山' THEN '华南'
when '杭州' THEN '华东'
when '上海' THEN '华东'
ELSE '其他' END AS temp,
SUM(q_num) AS s
from test_sql
GROUP BY case city_name
when '深圳' THEN '华南'
when '广州' THEN '华南'
when '佛山' THEN '华南'
when '杭州' THEN '华东'
when '上海' THEN '华东'
ELSE '其他' END;

脚本2（不规范写法）：

select case city_name
when '深圳' THEN '华南'
when '广州' THEN '华南'
when '佛山' THEN '华南'
when '杭州' THEN '华东'
when '上海' THEN '华东'
ELSE '其他' END AS temp,
SUM(q_num) AS s
from test_sql
GROUP BY temp;

脚本1和脚本2执行结果相同如下图：

脚本3：

select case
when q_num >= 3000 and pro_type = '女' then '1'
when q_num >= 2000 and q_num < 3000 and pro_type = '女' then '2'
when q_num >= 1000 and q_num < 2000 and pro_type = '女' then '3'
else '4' END as le,count(*) c
FROM test_sql
GROUP BY le

脚本3执行结果：

脚本4：

select city_name,
SUM(case when pro_type = '男' then q_num
else 0 END) as '男',
SUM(case when pro_type = '女' then q_num
else 0 END) as '女'
from test_sql
GROUP BY city_name

脚本4执行结果：