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