MySQL 中with rollup的用法，对分组再汇总

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

举例：

创建测试表

-- ----------------------------
-- 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)、()

cube(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、©、()

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