源表如上图所示,现在需要将每一年每月的销售情况统计出来做出一个交叉表
SELECT year_
,SUM(CASE WHEN month_='1' THEN amount ELSE NULL END) AS m1
,SUM(CASE WHEN month_='2' THEN amount ELSE NULL END) AS m2
,SUM(CASE WHEN month_='3' THEN amount ELSE NULL END) AS m3
,SUM(CASE WHEN month_='4' THEN amount ELSE NULL END) AS m4
,SUM(CASE WHEN month_='5' THEN amount ELSE NULL END) AS m5
,SUM(CASE WHEN month_='6' THEN amount ELSE NULL END) AS m5
FROM xiaoliu.sales
GROUP BY year_;
重点考察case when表达式,用搜索case when将六个月的amount都选出