mysql - MySQL：如何在 SELECT cluase 中使用 Multi-SELECT？

Question

我有一个这样的表，名称为aaa：

year   month amount
1991   1     1.1
1991   2     1.2
1991   3     1.3
1991   4     1.4
1992   1     2.1
1992   2     2.2
1992   3     2.3
1993   4     2.4

我想要这样的结果：

year m1   m2   m3   m4
1991 1.1  1.2  1.3  1.4
1992 2.1  2.2  2.3  2.4 

作为一个新手，经过几个小时的搜索和体验，我找不到出路。我试过这个但失败了：

SELECT year,
(SELECT amount FROM aaa WHERE month=1) AS m1,
(SELECT amount FROM aaa WHERE month=2) AS m2,
(SELECT amount FROM aaa WHERE month=3) AS m3,
(SELECT amount FROM aaa WHERE month=4) AS m4
FROM aaa GROUP BY year;  

任何人都可以告诉我吗？或者让我知道在哪里可以找到答案？提前谢谢。

或这个：

SELECT year,
(SELECT amount FROM aaa m WHERE month=1 AND m.year=aaa.year) AS m1,
(SELECT amount FROM aaa m WHERE month=2 AND m.year=aaa.year) AS m2,
(SELECT amount FROM aaa m WHERE month=3 AND m.year=aaa.year) AS m3,
(SELECT amount FROM aaa m WHERE month=4 AND m.year=aaa.year) AS m4
FROM aaa GROUP BY year;

标签： mysqlselectsubquery