mysql with rollup_MySQL-with rollup函数运用 _20160930

如果想在下面这个表下面添加一行 总计 数据行SQL代码怎么实现 并且根据9月金额进行城市降序 总计置于底部呢

MySQL提供了 group by with rollup 函数进行group by 字段的汇总

但是order by 互斥的不能同时用

第一步还是是先计算各城市每个月的金额

SELECT b.城市,SUM(IF(b.年月=201607,b.金额,NULL)) AS 7月金额,SUM(IF(b.年月=201608,b.金额,NULL)) AS 8月金额,SUM(IF(b.年月=201609,b.金额,NULL)) AS9月金额FROM(SELECT city AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS金额FROM test_a03order ASaGROUP BY city,DATE_FORMAT(order_time,"%Y%m")

)ASbGROUP BY b.城市

第二步我们先用group by with rollup 函数添加针对字段的汇总

SELECT b.城市,SUM(IF(b.年月=201607,b.金额,NULL)) AS 7月金额,SUM(IF(b.年月=201608,b.金额,NUL