mysql中的sql行列转换_mysql：sql行列转换

今天一个同学遇到一个问题问我了，由于本人平时学习的mysql比较基础，确实没解决，后来google了一下，才知道是sql的一种技法【行列转换】，话不多说先上图：

想得到下面的结果：

+------+-------+-------+-------+-------+

| 年份 | 1月 | 2月 | 11月 | 12月 |

+------+-------+-------+-------+-------+

| 2014 | 0 | 0 | 20000 | 21000 |

| 2015 | 30000 | 60000 | 0 | 0 |

+------+-------+-------+-------+-------+

先上数据样本(数据表是随意建的，差不多就那个意思)：

CREATE TABLE `order_sum` (

`id` int(11) unsigned NOT NULL AUTO_INCREMENT,

`year` smallint(4) unsigned NOT NULL,

`month` tinyint(3) unsigned NOT NULL,

`money` int(11) unsigned NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

准备插入的数据：

INSERT INTO `order_sum` VALUES (1, 2014, 11, 20000);

INSERT INTO `order_sum` VALUES (2, 2014, 12, 21000);

INSERT INTO `order_sum` VALUES (3, 2015, 1, 30000);

INSERT INTO `order_sum` VALUES (4, 2015, 2, 60000);

插入后的数据结构：

mysql> select * from order_sum;

+----+------+-------+-------+

| id | year | month | money |

+----+------+-------+-------+

| 1 | 2014 | 11 | 20000 |

| 2 | 2014 | 12 | 21000 |

| 3 | 2015 | 1 | 30000 |

| 4 | 2015 | 2 | 60000 |

+----+------+-------+-------+

4 rows in set (0.00 sec)

利用sum(if())来生成列，将对应的数据填充到对应的列下面

SELECT

YEAR AS '年份',

sum(IF(MONTH = 1, money, '0')) AS '1月',

sum(IF(MONTH = 2, money, '0')) AS '2月',

sum(IF(MONTH = 11, money, '0')) AS '11月',

sum(IF(MONTH = 12, money, '0')) AS '12月',

sum(money) AS total

FROM

order_sum

GROUP BY YEAR

如果要计算出每个月的平均数已经汇总的平均数，则要使用子句

SELECT

YEAR AS '年份',

sum(IF(MONTH = 1, money, '0')) AS '1月',

sum(IF(MONTH = 2, money, '0')) AS '2月',

sum(IF(MONTH = 11, money, '0')) AS '11月',

sum(IF(MONTH = 12, money, '0')) AS '12月',

sum(money) AS total

FROM

order_sum

GROUP BY YEAR

UNION ALL

SELECT

"总平均数",

ROUND(AVG(1月),2) ,

ROUND(AVG(2月),2),

ROUND(AVG(11月),2),

ROUND(AVG(12月),2),

ROUND(AVG(total),2)

FROM(

SELECT

YEAR AS "年份",

sum(IF(MONTH = 1, money, '0')) AS '1月',

sum(IF(MONTH = 2, money, '0')) AS '2月',

sum(IF(MONTH = 11, money, '0')) AS '11月',

sum(IF(MONTH = 12, money, '0')) AS '12月',

sum(money) AS total

FROM order_sum

GROUP BY YEAR

)tb2

这样就会得到如下的结果：

其中if可以用case when then else end来代替

sum(if(month=1,money,'0')) as '1月'

改成

max(case month when '1' then money else 0 end)  as '1月'

另外mysql行转列的函数：group_concat

mysql> select year,group_concat(money) from order_sum group by year;

+------+---------------------+

| year | group_concat(money) |

+------+---------------------+

| 2014 | 20000,21000 |

| 2015 | 30000,60000 |

+------+---------------------+

参考：http://blog.sina.com.cn/s/blog_4586764e0100lzmx.html