怎么把这样一个表：
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
1992 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
###准备工作###
新建一个表a
填入数据
INSERT INTO a (id,year, month, amount) VALUES ('0','1991', '1', '1.1');
INSERT INTO a (id,year, month, amount) VALUES ('1','1991', '2', '1.2');
INSERT INTO a (id,year, month, amount) VALUES ('2','1991', '3', '1.3');
INSERT INTO a (id,year, month, amount) VALUES ('3','1991', '4', '1.4');
INSERT INTO a (id,year, month, amount) VALUES ('4','1992', '1', '2.1');
INSERT INTO a (id,year, month, amount) VALUES ('5','1992', '2', '2.2');
INSERT INTO a (id,year, month, amount) VALUES ('6','1992', '3', '2.3');
INSERT INTO a (id,year, month, amount) VALUES ('7','1992', '4', '2.4');
##################
答案1：
select year,
(select amount from a as m where month=1 and m.year=a.year) as m1,
(select amount from a as m where month=2 and m.year=a.year) as m2,
(select amount from a as m where month=3 and m.year=a.year) as m3,
(select amount from a as m where month=4 and m.year=a.year) as m4
from a group by year;
运行查询结果为：
+------+--------------------+--------------------+--------------------+--------------------+
| year | m1 | m2 | m3 | m4 |
+------+--------------------+--------------------+--------------------+--------------------+
| 1991 | 1.100000023841858 | 1.2000000476837158 | 1.2999999523162842 | 1.399999976158142 |
| 1992 | 2.0999999046325684 | 2.200000047683716 | 2.299999952316284 | 2.4000000953674316 |
+------+--------------------+--------------------+--------------------+--------------------+
但是写成这样
select year,
(select amount from a as m where month=1 and a.year=a.year) as m1,
(select amount from a as m where month=2 and a.year=a.year) as m2,
(select amount from a as m where month=3 and a.year=a.year) as m3,
(select amount from a as m where month=4 and a.year=a.year) as m4
from a group by year;
就不对了。
很明显表a 和 别名m 并不等同，m.year=a.year 这种用法的原理是什么
别名的生成过程和生成原理没找到资料， 还大神不吝赐教谢谢！
