MySQL|MySQL 中with rollup的用法，对分组再汇总数据库|mysql

mysql的rollup 主要作用: 对group by分组的结果再进行汇总

文章目录

创建测试表
运行不带with rollup
运行带with rollup

举例：
创建测试表

-- ---------------------------- -- Table structure for test1 -- ---------------------------- DROP TABLE IF EXISTS `test1`; CREATE TABLE `test1`( `id` int(11) NOT NULL, `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `sex` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL, `money` int(11) NULL DEFAULT NULL, PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic; -- ---------------------------- -- Records of test1 -- ---------------------------- INSERT INTO `test1` VALUES (1, '靓仔', '1', 10); INSERT INTO `test1` VALUES (2, '靓妹', '1', 2); INSERT INTO `test1` VALUES (3, '靓仔', '1', 30); INSERT INTO `test1` VALUES (4, 'test', '2', 7); INSERT INTO `test1` VALUES (5, '靓妹', '2', 6); INSERT INTO `test1` VALUES (6, '靓仔', '2', 5); INSERT INTO `test1` VALUES (7, '靓妹', '2', 5);

文章图片

运行不带with rollup

mysql> SELECT name,sex,sum(money) from test1 group by `name`,sex; +-------+-----+------------+ | name | sex | sum(money) | +-------+-----+------------+ | test| 2| 7| | 靓仔| 1| 40| | 靓仔| 2| 5| | 靓妹| 1| 2| | 靓妹| 2| 11| +-------+-----+------------+ 5 rows in set (0.07 sec)

运行带with rollup

mysql> SELECT name,sex,sum(money) from test1 group by `name`,sex with rollup; +-------+------+------------+ | name | sex| sum(money) | +-------+------+------------+ | test| 2| 7| | test| NULL | 7| | 靓仔| 1| 40| | 靓仔| 2| 5| | 靓仔| NULL | 45| | 靓妹| 1| 2| | 靓妹| 2| 11| | 靓妹| NULL | 13| | NULL| NULL | 65| +-------+------+------------+ 9 rows in set (0.07 sec)

可以看到每个分组最后会多一条结果，就是对每个分组聚合结果的汇总，然后最最后还有个汇总的汇总，额……有点绕，也就是小计和合计
看图：
![在这里插入图片描述](https://img-blog.csdnimg.cn/98a1fc1959c9422595aa73590ebeec4f.png

文章图片

等价于如下代码：

SELECT name,sex,sum(money) from test1 group by `name`,sex union SELECT name,null,sum(money) from test1 group by `name` union SELECT null,null,sum(money) from test1 ;

+-------+------+------------+ | name | sex| sum(money) | +-------+------+------------+ | test| 2| 7| | 靓仔| 1| 40| | 靓仔| 2| 5| | 靓妹| 1| 2| | 靓妹| 2| 11| | test| NULL | 7| | 靓仔| NULL | 45| | 靓妹| NULL | 13| | NULL| NULL | 65| +-------+------+------------+ 9 rows in set (0.09 sec)

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。虽然两者得到相同的结果，但是执行计划却不同，rollup只需要一次表扫描操作就能得到全部结果，因此查询效率在此得到了极大的提升。

本以为MySQL也有像hive一样的cube来做多维查询，结果它不支持
在mysql5.6.17版本中，
只定义了cube，但是不支持cube操作。

二、cube：
rollup是cube的一种特殊情况，和rollup一样，cube也是一种对数据的聚合操作。但是rollup只在层次上对数据进行聚合，而cube对所有的维度进行聚合。具有N个维度的列，cube需要2的N次方次分组操作，而rollup只需要N次分组操作。
在mysql5.6.17版本中，只定义了cube，但是不支持cube操作。
2、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)、()
【MySQL|MySQL 中with rollup的用法，对分组再汇总】cube(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、?、()

参考链接：mysql之rollup(…)和cube(…)