表格:
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执行结果: