Mr_Cxy

导航

MySQL交叉表处理_20160923

交叉表处理,在二维表中例如下面表 想把年月字段放到列字段,在sql中可以使用sum(if(条件,求和字段,null)) 函数来进行行列的转置

1、首先是上篇的年月字段在一列

1 SELECT city AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金额
2 FROM test_a03order AS a
3 GROUP BY city,DATE_FORMAT(order_time,"%Y%m")
年月为单位

2、将年月字段放到列字段

1 SELECT b.城市,SUM(IF(b.年月=201607,b.金额,NULL)) AS 7月金额,SUM(IF(b.年月=201608,b.金额,NULL)) AS 8月金额,SUM(IF(b.年月=201609,b.金额,NULL)) AS 9月金额
2 FROM (
3     SELECT city AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金额
4     FROM test_a03order AS a
5     GROUP BY city,DATE_FORMAT(order_time,"%Y%m")
6 ) AS b
7 GROUP BY b.城市
行转列

 

posted on 2016-09-23 09:52  Mr_Cxy  阅读(734)  评论(0编辑  收藏  举报