MySQL交叉表处理_20160923

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

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

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

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

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月金额
FROM (
    SELECT city AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金额
    FROM test_a03order AS a
    GROUP BY city,DATE_FORMAT(order_time,"%Y%m")
) AS b
GROUP BY b.城市