MySQL-with rollup函数运用 _20160930
2024-09-05 17:03:31
在博客里http://www.cnblogs.com/Mr-Cxy/p/5898839.html提到了行转列,
如果想在下面这个表下面添加一行 总计 数据行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.城市
第二步我们先用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 a
GROUP 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 a
GROUP 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,'空城市') 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.城市 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,'空城市') 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.城市 WITH ROLLUP
) AS c
ORDER BY FIELD(城市,'总计'),c.9月金额 DESC
最新文章
- Excel
- Flask-WTF form doesn't have attribute 'validate_on_submit'问题
- 解決BufferedReader读取UTF-8文件中文乱码
- C# 将透明图片的非透明区域转换成Region
- Hadoop的RPC框架介绍
- [Javascript] Immutable opreators
- bzoj1211
- spring web flow 2.0入门(转)
- Delphi 常用函数记录
- 51Nod 1090 3个数和为0 set 二分优化
- .NET平台开源项目速览(19)Power BI神器DAX Studio
- MySQL大量数据入库的性能比较
- docker删除已经停止的容器
- TIMESTAMPDIFF用法
- 解决GitHub下载速度比较慢
- Python3基础 list 访问列表中的列表的元素
- Day 48 HTML 语言Day1
- python 列表的内容赋值
- MySQL 根据年、季度、月、周、日统计数据
- Android 6.0+ 运行时权限
热门文章
- Flash威胁的不不过浏览器
- 聊聊高并发(三十九)解析java.util.concurrent各个组件(十五) 理解ExecutorService接口的设计
- MySQL数据表导出某条记录
- 贷前系统ElasticSearch实践总结
- IOS - unity3d错误Could not produce class with ID
- iOSPOI检索详细总结
- Unix环境高级编程第三版中实例代码如何在自己的linux上运行的问题
- vs2013 solution文件解析
- packages/wepy-web/src/wx.js 分析storage 的加载原理 wx.getStorage(OBJECT)
- 【题解】Cats Transport (斜率优化+单调队列)