【SQL】行转列问题

@SQL

已知：

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

创建这张表格

-- 在库下创建表
create table table1(
   year int,
   month int ,
   amount double) ;
--  插入数据
insert into table1 values
          (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);

行专列的关键语句：

简写if：

select year,
       sum(if(month=1,amount,0)) m1,
       sum(if(month=2,amount,0)) m2,
       sum(if(month=3,amount,0)) m3,
       sum(if(month=4,amount,0)) m4
from table1
group by year;

伪列理解

select year,
       sum(a) as m1,
       sum(b) as m2,
       sum(c) as m3,
       sum(d) as m4
from
    (select *,
            -- a是伪列，我们自己造的。
            if(month=1,amount,0) a,
            if(month=2,amount,0) b,
            if(month=3,amount,0) c,
            if(month=4,amount,0) d
     from table1) t
group by t.year;

case when

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