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