SQL面试题练习-实现pivot行列转换

题目：实现把表t1的数据变成表t2的形式

          t1

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

                                              t2

year

m1

m2

m3

m4

1991

1.1

1.2

1.3

1.4

1992

2.1

2.2

2.3

2.4

 

1. 一般方法

抓住修改之后的表的每一列的特征，也就是说第n列month值为n；select后面选取出列是针对某一行来说的，这样对于某一行的各个列之间可以作运算，脑洞大开一下，可以想到下面几种方法：

1. 巧妙的使用sign()函数

createdatabase practice;usepractice;createtable t1     (         year int not null,         month int not null,         amount dec(2,1)     );insertinto t1 values('1991','1','1.1');    insert into t1 values('1991','2','1.2');    insert into t1 values('1991','3','1.3');    insert into t1 values('1991','4','1.4');    insert into t1 values('1992','1','2.1');    insert into t1 values('1992','2','2.2');    insert into t1 values('1992','3','2.3');    insert into t1 values('1992','4','2.4');  createtable t2 asselectyear,  sum(amount*(1-abs(sign(month-1))))as m1,  sum(amount*(1-abs(sign(month-2))))as m2,  sum(amount*(1-abs(sign(month-3))))as m3,       sum(amount*(1-abs(sign(month-4)))) as m4fromt1        group by year;select*from t2;

可以看到达到想要的目的了：

 

2. 使用case…when…then…else…end结构：

select year,max(case month when '1' then amount else 0 end ) as m1,max(casemonth when '2' then amount  else 0end)  as m2,max(casemonth when '3' then amount else 0 end ) as m3,max(casemonth when '4' then amount else 0 end )as m4from t1group by year;

3. 既然如此还有一种办法：用 if(…,…,)，而且如果非的情况为0，MAX()可以改为SUM()：

select year,  sum(if (month ='1',amount,0))asm1,  sum(if (month ='2',amount,0))asm2,  sum(if (month ='3',amount,0))asm3,  sum(if (month ='4',amount,0))asm4from t1group by year;

2. 使用pivot()函数

mysql中似乎没有pivot函数。pivot()函数一般形式为：

table_source

PIVOT

(

    <聚合函数>(<要聚合的列>)

FOR

[<包含要成为列标题的值的列>]

    IN ( [第一个透视的列], [第二个透视的列],

    ... [最后一个透视的列])

) AS <透视表的别名>

select year ,     [1] as m1,     [2] as m2,     [3] as m3,     [4] as m4from t1pivot(      sum(amount)      for month in ([1],[2],[3],[4])      )      as pvt;