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
最新文章
- 逻辑推理:在一个100条语句的列表中,第n条语句是“在这个列表中,恰有n条语句为假”,可以得出什么结论?
- 从clone()谈protected
- Linux的文件/目录访问权限
- [转]XML中必须进行转义的字符
- Redis 介绍与安装
- 贝叶斯推断 &;&; 概率编程初探
- Spring再接触 Scope范围
- 20165223《Java程序设计》第七周Java学习总结
- java将long数据转为int类型的方法
- 批量插入数据, 将DataTable里的数据批量写入数据库的方法
- SpringMVC教程4
- 无法从其“Checked”属性的字符串表示形式“checked”创建“System.Boolean”类型
- pythoner国内比较快 的 镜像源
- three.js 初探
- 用 Python 替代 Bash 脚本(转)
- Hadoop操作前准备工作
- map以自定义类型当Key
- dva subscription的使用方法
- MIPI调试经验(转载)
- ETL 工具下载全集 包括 Informatica Datastage Cognos( 持续更新)