作者：姝susuju_110 | 来源：互联网 | 2024-10-12 17:48
MySQL-withrollup函数运用_20160930在博客里http:www.cnblogs.comMr-Cxyp5898839.html提到了行转列，(ht
MySQL-with rollup函数运用 _20160930
在博客里http://www.cnblogs.com/Mr-Cxy/p/5898839.html提到了行转列，(http://blog.csdn.net/ybygjy/article/details/41557425)行转列 列转行都有
如果想在下面这个表下面添加一行 总计 数据行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 aGROUP BY city,DATE_FORMAT(order_time,"%Y%m")
) AS b
GROUP BY b.城市
第二步我们先用group by with rollup 函数添加针对字段的汇总
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 aGROUP BY city,DATE_FORMAT(order_time,"%Y%m")
) AS b
GROUP BY b.城市 WITH ROLLUP
这个记录没有出现总计两个字，怎么实现呢 继续修改代码 ifnull()函数
第三 添加总计字样(有坑) ifnull()函数是将空字段另外命名
SELECT IFNULL(b.城市,"总计") AS 城市,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 aGROUP BY city,DATE_FORMAT(order_time,"%Y%m")
) AS b
GROUP BY b.城市 WITH ROLLUP
第四 摆脱掉坑
为什么说有坑呢 如果ifnull()函数放在上面代码位置 看似实现了总计的字样 ifnull()是针对用了with rollup 函数总计这个位置出现空字段时候修改它为总计字样的,
如果城市这一列里本身就含有没有命名的城市呢 这一列就会出现多个总计字样 因此我们需要将ifnull()函数放到嵌套的子表里 将空字段在子表里面就预先改成别名（不能是总计字样）
因此 使用2个ifnull()函数就不会有这样的问题 结果是一样的
SELECT IFNULL(b.城市,"总计") AS 城市,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 IFNULL(city,&＃39;空城市&＃39;) AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金额FROM test_a03order AS aGROUP BY city,DATE_FORMAT(order_time,"%Y%m")
) AS b
GROUP BY b.城市 WITH ROLLUP
第五 排序
在这篇博客http://www.cnblogs.com/Mr-Cxy/p/5910291.html 提到了order by field()自定义排序函数 如下图红框所示有这样一个默认的自定义排序规则
因此 如果想实现 根据9月金额进行城市降序 总计置于底部 效果 可以把上面代码当成一个子表嵌套 结合 order by field()自定义函数实现
SELECT c.*
FROM (SELECT IFNULL(b.城市,"总计") AS 城市,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 IFNULL(city,&＃39;空城市&＃39;) AS 城市,DATE_FORMAT(order_time,"%Y%m") AS 年月,SUM(pay_money) AS 金额FROM test_a03order AS aGROUP BY city,DATE_FORMAT(order_time,"%Y%m")) AS bGROUP BY b.城市 WITH ROLLUP
) AS c
ORDER BY FIELD(城市,&＃39;总计&＃39;),c.9月金额 DESC
京公网安备 11010802041100号