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.城市
