MySQL ROLLUP
简介：在本教程中，您将学习如何使用MySQL
ROLLUP子句生成小计和总计。
设置样本表
以下语句创建一个的新表名为sales，用于存储按产品系列和年份汇总的订单值。数据来自于products，orders和orderDetails中的表样本数据库。
CREATE TABLE sales
SELECT
productLine,
YEAR(orderDate) orderYear,
quantityOrdered * priceEach orderValue
FROM
orderDetails
INNER JOIN
orders USING (orderNumber)
INNER JOIN
products USING (productCode)
GROUP BY
productLine ,
YEAR(orderDate);
以下查询返回表中的所有行sales：
SELECT
*
FROM
sales;
运行结果：
+------------------+-----------+------------+ | productLine | orderYear | orderValue | +------------------+-----------+------------+ | Classic Cars | 2013 | 5571.80 | | Classic Cars | 2014 | 8124.98 | | Classic Cars | 2015 | 5971.35 | | Motorcycles | 2013 | 2440.50 | | Motorcycles | 2014 | 2598.77 | | Motorcycles | 2015 | 4004.88 | | Planes | 2013 | 4825.44 | | Planes | 2014 | 2857.35 | | Planes | 2015 | 4018.00 | ...
MySQL ROLLUP概述
分组集是要分组的一组列。例如，以下查询创建一个表示的分组集(productline)
SELECT
productline,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline;
运行结果：
+------------------+-----------------+ | productline | totalOrderValue | +------------------+-----------------+ | Classic Cars | 19668.13 | | Motorcycles | 9044.15 | | Planes | 11700.79 | | Ships | 13147.86 | | Trains | 9021.03 | | Trucks and Buses | 14194.95 | | Vintage Cars | 12245.78 | +------------------+-----------------+ 7 rows in set (0.00 sec)以下查询创建一个空分组集，表示为
()：
SELECT
SUM(orderValue) totalOrderValue
FROM
sales;
运行结果：
+-----------------+ | totalOrderValue | +-----------------+ | 89022.69 | +-----------------+ 1 row in set (0.00 sec)如果要在一个查询中一起生成两个或多个分组集，使用
UNION ALL按如下方式使用运算符：
SELECT
productline,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline
UNION ALL
SELECT
NULL,
SUM(orderValue) totalOrderValue
FROM
sales;
这是查询输出：
+------------------+-----------------+ | productline | totalOrderValue | +------------------+-----------------+ | Classic Cars | 19668.13 | | Motorcycles | 9044.15 | | Planes | 11700.79 | | Ships | 13147.86 | | Trains | 9021.03 | | Trucks and Buses | 14194.95 | | Vintage Cars | 12245.78 | | NULL | 89022.69 | +------------------+-----------------+ 8 rows in set (0.01 sec)因为
UNION ALL要求所有查询具有相同数量的列，所以我们在第二个查询的选择列表中添加了NULL满足此要求的内容。
将NULL在productLine列中标识量线的总计。
此查询能够按产品线以及总计行生成总订单值。但是，它有两个问题：
- 查询非常冗长。
- 查询的性能可能不太好，因为数据库引擎必须在内部执行两个单独的查询并将结果集合并为一个。
ROLLUP子句。
ROLLUP子句是GROUP BY子句的扩展，具有以下语法：
SELECT
select_list
FROM
table_name
GROUP BY
c1, c2, c3 WITH ROLLUP;
根据子句中ROLLUP指定的列或表达式生成多个分组集GROUP BY。
请参阅以下查询：
SELECT
productLine,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline WITH ROLLUP;
输出结果：
+------------------+-----------------+ | productLine | totalOrderValue | +------------------+-----------------+ | Classic Cars | 19668.13 | | Motorcycles | 9044.15 | | Planes | 11700.79 | | Ships | 13147.86 | | Trains | 9021.03 | | Trucks and Buses | 14194.95 | | Vintage Cars | 12245.78 | | NULL | 89022.69 | +------------------+-----------------+ 8 rows in set (0.00 sec)如输出中清楚地显示的那样，
ROLLUP子句不仅生成小计，还生成订单值的总计。
如果在GROUP BY子句中指定了多个列，则ROLLUP子句假定输入列之间存在优先级结构。
例如：
GROUP BY c1, c2, c3 WITH ROLLUP
ROLLUP假设有以下优先级：
c1 > c2 > c3它会生成以下分组集：
(c1, c2, c3) (c1, c2) (c1) ()如果您在
GROUP BY子句中指定了两列：
GROUP BY c1, c2 WITH ROLLUP然后
ROLLUP生成以下分组集：
(c1, c2) (c1) ()请参阅以下查询示例：
SELECT
productLine,
orderYear,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
productline,
orderYear
WITH ROLLUP;
输出结果：
+------------------+-----------+-----------------+ | productLine | orderYear | totalOrderValue | +------------------+-----------+-----------------+ | Classic Cars | 2013 | 5571.80 | | Classic Cars | 2014 | 8124.98 | | Classic Cars | 2015 | 5971.35 | | Classic Cars | NULL | 19668.13 | | Motorcycles | 2013 | 2440.50 | | Motorcycles | 2014 | 2598.77 | | Motorcycles | 2015 | 4004.88 | | Motorcycles | NULL | 9044.15 | ...
ROLLUP在每次产品线更改时生成小计行，在结果结束时生成总计。
在这种情况下的层次结构是：
productLine > orderYear如果您反转层次结构，例如：
SELECT
orderYear,
productLine,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
orderYear,
productline
WITH ROLLUP;
下图显示了输出：
+-----------+------------------+-----------------+ | orderYear | productLine | totalOrderValue | +-----------+------------------+-----------------+ | 2013 | Classic Cars | 5571.80 | | 2013 | Motorcycles | 2440.50 | | 2013 | Planes | 4825.44 | | 2013 | Ships | 5072.71 | | 2013 | Trains | 2770.95 | | 2013 | Trucks and Buses | 3284.28 | | 2013 | Vintage Cars | 4080.00 | | 2013 | NULL | 28045.68 | | 2014 | Classic Cars | 8124.98 | | 2014 | Motorcycles | 2598.77 | | 2014 | Planes | 2857.35 | | 2014 | Ships | 4301.15 | | 2014 | Trains | 4646.88 | | 2014 | Trucks and Buses | 4615.64 | | 2014 | Vintage Cars | 2819.28 | | 2014 | NULL | 29964.05 | | 2015 | Classic Cars | 5971.35 | | 2015 | Motorcycles | 4004.88 | | 2015 | Planes | 4018.00 | | 2015 | Ships | 3774.00 | | 2015 | Trains | 1603.20 | | 2015 | Trucks and Buses | 6295.03 | | 2015 | Vintage Cars | 5346.50 | | 2015 | NULL | 31012.96 | | NULL | NULL | 89022.69 | +-----------+------------------+-----------------+ 25 rows in set (0.00 sec)
ROLLUP每年更改时生成小计，并在结果集的末尾生成总计。
此示例中的层级结构是：
orderYear > productLine
GROUPING() 函数
要检查NULL结果集中是否表示小计或总计，请使用GROUPING()函数。
当NULL在超级聚合时，GROUPING()函数返回1 ，否则返回0。
GROUPING()函数可用于查询，HAVING子句和（从MySQL 8.0.12开始）ORDER BY子句。
请考虑以下查询：
SELECT
orderYear,
productLine,
SUM(orderValue) totalOrderValue,
GROUPING(orderYear),
GROUPING(productLine)
FROM
sales
GROUP BY
orderYear,
productline
WITH ROLLUP;
下图显示了输出：
+-----------+------------------+-----------------+---------------------+-----------------------+ | orderYear | productLine | totalOrderValue | GROUPING(orderYear) | GROUPING(productLine) | +-----------+------------------+-----------------+---------------------+-----------------------+ | 2013 | Classic Cars | 5571.80 | 0 | 0 | | 2013 | Motorcycles | 2440.50 | 0 | 0 | | 2013 | Planes | 4825.44 | 0 | 0 | | 2013 | Ships | 5072.71 | 0 | 0 | | 2013 | Trains | 2770.95 | 0 | 0 | | 2013 | Trucks and Buses | 3284.28 | 0 | 0 | | 2013 | Vintage Cars | 4080.00 | 0 | 0 | | 2013 | NULL | 28045.68 | 0 | 1 | | 2014 | Classic Cars | 8124.98 | 0 | 0 | | 2014 | Motorcycles | 2598.77 | 0 | 0 | | 2014 | Planes | 2857.35 | 0 | 0 | | 2014 | Ships | 4301.15 | 0 | 0 | | 2014 | Trains | 4646.88 | 0 | 0 | | 2014 | Trucks and Buses | 4615.64 | 0 | 0 | | 2014 | Vintage Cars | 2819.28 | 0 | 0 | | 2014 | NULL | 29964.05 | 0 | 1 | | 2015 | Classic Cars | 5971.35 | 0 | 0 | | 2015 | Motorcycles | 4004.88 | 0 | 0 | | 2015 | Planes | 4018.00 | 0 | 0 | | 2015 | Ships | 3774.00 | 0 | 0 | | 2015 | Trains | 1603.20 | 0 | 0 | | 2015 | Trucks and Buses | 6295.03 | 0 | 0 | | 2015 | Vintage Cars | 5346.50 | 0 | 0 | | 2015 | NULL | 31012.96 | 0 | 1 | | NULL | NULL | 89022.69 | 1 | 1 | +-----------+------------------+-----------------+---------------------+-----------------------+ 25 rows in set (0.00 sec)当
NULL在orderYear 列发生在行聚合时， GROUPING(orderYear)时返回1 ，否则为0。
类似地，当NULL在productLine列发生行聚合时，GROUPING(productLine)当返回1 ，否则为0。
我们经常使用GROUPING()函数将有意义的标签替换为超级聚合NULL值，而不是直接显示它。
以下示例显示如何将IF()函数与GROUPING()函数组合以替换超聚合NULL值orderYear和productLine列中的标签：
SELECT
IF(GROUPING(orderYear),
'All Years',
orderYear) orderYear,
IF(GROUPING(productLine),
'All Product Lines',
productLine) productLine,
SUM(orderValue) totalOrderValue
FROM
sales
GROUP BY
orderYear ,
productline
WITH ROLLUP;
输出结果：
+-----------+-------------------+-----------------+ | orderYear | productLine | totalOrderValue | +-----------+-------------------+-----------------+ | 2013 | Classic Cars | 5571.80 | | 2013 | Motorcycles | 2440.50 | | 2013 | Planes | 4825.44 | | 2013 | Ships | 5072.71 | | 2013 | Trains | 2770.95 | | 2013 | Trucks and Buses | 3284.28 | | 2013 | Vintage Cars | 4080.00 | | 2013 | All Product Lines | 28045.68 | | 2014 | Classic Cars | 8124.98 | | 2014 | Motorcycles | 2598.77 | | 2014 | Planes | 2857.35 | | 2014 | Ships | 4301.15 | | 2014 | Trains | 4646.88 | | 2014 | Trucks and Buses | 4615.64 | | 2014 | Vintage Cars | 2819.28 | | 2014 | All Product Lines | 29964.05 | | 2015 | Classic Cars | 5971.35 | | 2015 | Motorcycles | 4004.88 | | 2015 | Planes | 4018.00 | | 2015 | Ships | 3774.00 | | 2015 | Trains | 1603.20 | | 2015 | Trucks and Buses | 6295.03 | | 2015 | Vintage Cars | 5346.50 | | 2015 | All Product Lines | 31012.96 | | All Years | All Product Lines | 89022.69 | +-----------+-------------------+-----------------+ 25 rows in set, 2 warnings (0.00 sec)在本教程中，您已经学习了如何使用MySQL
ROLLUP()生成多个分组集，同时考虑GROUP BY子句中指定的列之间的层级结构。 