MySQL中with rollup的用法及说明
MySQL with rollup的用法
with rollup概述
应用实例
MySQL with rollup 聚合函数类似oracle cube操作
作用
题目示例
MySQL with rollup的用法当需要对数据库数据进行分类统计的时候，往往会用上groupby进行分组。
而在groupby后面还可以加入withcube和withrollup等关键字对数据进行汇总。
mysql文档
with rollup概述with在sql语句中定义在group by之后。当需要对数据库数据进行分类统计的时候，往往会用上groupby进行分组。
而在groupby后面还可以加入withcube和withrollup等关键字对数据进行汇总。
不过这个cube在mysql中并不适用。
现在有这样一张学生表，里面的数据如下所示。
如果想对根据学生，对科目，分数求和，可以这样写。
如果想在这个的基础上，求出学生的总分数，应该怎么做。
使用 WITH ROLLUP，此函数是对聚合函数进行求和，注意 with rollup是对 group by 后的第一个字段，进行分组求和。
ORDER BY不能在rollup中使用，两者为互斥关键字，如果使用，会抛出以下错误：Error Code:1221. Incorrect usage of CUBE/ROLLUP and ORDER BY。mysql5.7中是不支持的，在8.0以后支持。
相信大家已经知道如何使用with rollup了，这个就是在group by分组之后，再次对聚合函数进行求和。
MySQL with rollup 聚合函数类似oracle cube操作 作用在分组统计数据的基础上再进行统计汇总
题目示例
根据题目意思需要 求每个价格区间，时间的mark=0的和/mark=1的和
最后还有求个所有价格区间 按月份的mark=0的和/mark=1的和
SELECT
PriceBand,
YearMonth,
sum( CASE Mark WHEN 0 THEN VALUE END ) / sum( CASE Mark WHEN 1 THEN VALUE END )
FROM
test
GROUP BY
YearMonth,
PriceBand UNION ALL
SELECT
'Alll priceBand',
YearMonth,
sum( CASE Mark WHEN 0 THEN VALUE END ) / sum( CASE Mark WHEN 1 THEN VALUE END )
FROM
test
GROUP BY
YearMonth
在不知道mysql有 这种聚合函数的情况 我所想到的只能靠这样拼接来实现
使用rollup后
SELECT
ifnull( PriceBand, 'All priceBand' ),
YearMonth,
sum( CASE Mark WHEN 0 THEN VALUE END ) / sum( CASE Mark WHEN 1 THEN VALUE END )
FROM
test
GROUP BY
YearMonth,
PriceBand WITH ROLLUP
HAVING
YearMonth IS NOT NULL
注意
在mysql5.6.17版本中，只定义了cube，但是不支持cube操作。
cube也是一种对数据的聚合操作。但是rollup只在层次上对数据进行聚合，而cube对所有的维度进行聚合。具有N个维度的列，cube需要2的N次方次分组操作，而rollup只需要N次分组操作。
rollup和cube的区别：
1）假设有n个维度，rollup会有n个聚合：
rollup(a,b) 统计列包含：(a,b)、(a)、()
rollup(a,b,c)统计列包含：(a,b,c)、(a,b)、(a)、()
2）假设有n个纬度，cube会有2的n次方个聚合
cube(a,b) 统计列包含：(a,b)、(a)、(b)、()
cube(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、©、()
在mysql中 with rollup放的位置是有要求的
之前的group by 正常使用分组 加了 with rollup 在那个字段后就对所有价格区间进行统计 同时 with rollup 并不能放在 (group by a with rollup,b )中间
以上为个人经验，希望能给大家一个参考，也希望大家多多支持软件开发网。