mysql with rollup_MySQL-with rollup函数运用

如果想在下面这个表下面添加一行 总计 数据行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)) 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.城市