MySQL分组行列转换(面试题)

最近有小朋友去面试,拍回来几道面试题,解决一下

先在自己本地的数据库插入上图的表

create table year1(
     year int,
     month int,
     amount double
);

insert into year1 values (1991,1,1.1);
insert into year1 values (1991,2,1.2);
insert into year1 values (1991,3,1.3);
insert into year1 values (1991,4,1.4);
insert into year1 values (1992,1,2.1);
insert into year1 values (1992,2,2.2);
insert into year1 values (1992,3,2.3);
insert into year1 values (1992,4,2.4);

原数据:

在mysql中可以用case when 来做行列转换,然后对year做分组聚合就可以得到结果:

select  year Year,
       sum(case when month=1 then amount end) as m1,
       sum(case when month=2 then amount end) as m2,
       sum(case when month=3 then amount end) as m3,
       sum(case when month=4 then amount end) as m4
from year1 group by year;

结果:

用sum()函数是因为需要对year分组聚合,对于这道题来说这样就可以解决了,但是总觉得不是很满意,由于技术有限还没想到其他解决办法,如果有其他方法,请大神在下面评论.