问题
答案
SELECT CASE WHEN pref_name in ('德岛', '香川', '爱媛', '高知') THEN '四国'
WHEN pref_name in ('福冈', '佐贺', '长崎') THEN '九州'
ELSE '其他' END AS distinct,
SUM(population)
FROM PopTbl
--- 因为根据SQL标准, SELECT是在GROUP BY后执行的, 所以这里重复使用了前面的CASE表达式
GROUP BY CASE WHEN pref_name in ('德岛', '香川', '爱媛', '高知') THEN '四国'
WHEN pref_name in ('福冈', '佐贺', '长崎') THEN '九州'
ELSE '其他' END
按照人口数量等级进行统计
SELECT CASE WHEN population < 100 THEN '01'
WHEN populaiton >= 100 AND population < 200 THEN '02'
WHEN population >= 200 AND population < 300 THEN '03'
WHEN population >- 300 THEN '04'
ELSE NULL END AS pop_class,
COUNT(*) AS cnt
FROM PopTbl
GROUP BY CASE WHEN population < 100 THEN '01'
WHEN populaiton >= 100 AND population < 200 THEN '02'
WHEN population >= 200 AND population < 300 THEN '03'
WHEN population >- 300 THEN '04'
ELSE NULL END
