最近遇到一个问题：

把上面表格的数据查询成：

看到这样的需求，首先想到的是用case去统计以及 用group by来分组

第一版sql代码：

SELECT `year`, (CASE WHEN amount = 1 THEN num END) AS n1, (CASE WHEN amount = 2 THEN num END) AS n2, (CASE WHEN amount = 3 THEN num END) AS n3FROM testGROUP BY `year`

查询出来的结果有点不如人意：

这么说明了分组之后只显示到第一行数据，那么我们去掉分组看看：

 SELECT `year`, (CASE WHEN amount = 1 THEN num END) AS n1, (CASE WHEN amount = 2 THEN num END) AS n2, (CASE WHEN amount = 3 THEN num END) AS n3 FROM test

有点像我们想要的了，只是没有分组以及去掉空值

而且我们可以看出，在分组的情况下m1,m2,m3的值都是一个最大值来的

所以我们可以用一个子查询来查询上面的结果集中分组的最大值 最终版sql：

SELECT `year`, MAX(n1) AS m1, MAX(n2) AS m2, MAX(n3) AS m3FROM ( SELECT `year`, (CASE WHEN amount = 1 THEN num END) AS n1, (CASE WHEN amount = 2 THEN num END) AS n2, (CASE WHEN amount = 3 THEN num END) AS n3 FROM test ) AS aGROUP BY `year`

最终可以得到我们想要的结果：