SQL校招社招刷题系列之交叉表的制作

源表

源表如上图所示，现在需要将每一年每月的销售情况统计出来做出一个交叉表

解答

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都选出