问题

今天看到一个mysql行转列的问题

表1 转换为 表2

表1

year month amount
2011 1 1.1
2011 2 1.2
2011 3 1.3
2011 4 1.4
2012 1 2.1
2012 2 2.2
2012 3 2.3
2012 4 2.4

表2

year m1 m2 m3 m4
2011 1.1 1.2 1.3 1.4
2012 2.1 2.2 2.3 2.4

方法

百度获得以下两种方法

CASE WHEN

1
2
3
4
5
6
7
8
9
SELECT 
`year`,
MAX( CASE WHEN `month` = 1 THEN amount ELSE 0 END) as "m1",
MAX( CASE WHEN `month` = 2 THEN amount ELSE 0 END) as "m2",
MAX( CASE WHEN `month` = 3 THEN amount ELSE 0 END) as "m3",
MAX( CASE WHEN `month` = 4 THEN amount ELSE 0 END) as "m4"
FROM
test
GROUP BY `year`;

GROUP_CONCAT

1
2
3
4
5
6
7
8
SELECT 
year,
SUBSTRING_INDEX(SUBSTRING_INDEX(str, ",", -4),",",1) as m1 ,
SUBSTRING_INDEX(SUBSTRING_INDEX(str, ",", -3),",",1) as m2 ,
SUBSTRING_INDEX(SUBSTRING_INDEX(str, ",", -2),",",1) as m3 ,
SUBSTRING_INDEX(SUBSTRING_INDEX(str, ",", -1),",",1) as m4
from
(SELECT year, GROUP_CONCAT(amount) as str FROM test GROUP BY `year`) as t;

参考文档

  1. http://hchmsguo.iteye.com/blog/555543
  2. https://www.cnblogs.com/crystaltu/p/6699392.html
  3. https://www.oschina.net/question/3937294_2285120