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
准备sql语句：
drop table if exists sales;
create table sales(id int auto_increment primary key,year varchar(10), month varchar(10), amount float(2,1));
insert into sales values
(null,'1991','1',1.1),
(null,'1991','2',1.2),
(null,'1991','3',1.3),
(null,'1991','4',1.4),
(null,'1992','1',2.1),
(null,'1992','2',2.2),
(null,'1992','3',2.3),
(null,'1992','4',2.4);
答案一：
select s.year ,
(select t.amount from sales t where t.month='1' and t.year= s.year) m1,
(select t.amount from sales t where t.month='2' and t.year= s.year) m2,
(select t.amount from sales t where t.month='3' and t.year= s.year) m3,
(select t.amount from sales t where t.month='4' and t.year= s.year) m4
from sales s
group by s.year;
答案二：
select
y.year,
(select t.amount from sales t where t.month='1' and t.year= y.year) m1,
(select t.amount from sales t where t.month='2' and t.year= y.year) m2,
(select t.amount from sales t where t.month='3' and t.year= y.year) m3,
(select t.amount from sales t where t.month='4' and t.year= y.year) m4
from
(select distinct year from sales s order by year) y
