12.20.2 GROUP BY 修饰符_MySQL 5.7 参考手册

MySQL 8.0 参考手册 / 第 12 章函数和运算符 / 12.20聚合函数 / 12.20.2 GROUP BY 修饰符

12.20.2 GROUP BY 修饰符

该GROUP BY子句允许一个WITH ROLLUP修饰符，该修饰符会导致摘要输出包含表示更高级别（即超级聚合）摘要操作的额外行。ROLLUP 因此，您可以使用单个查询在多个分析级别回答问题。例如， ROLLUP可用于为 OLAP（联机分析处理）操作提供支持。

假设一个sales表有 year、country、 product和profit 列用于记录销售利润率：

CREATE TABLE sales
(
    year    INT,
    country VARCHAR(20),
    product VARCHAR(32),
    profit  INT
);

要总结每年的表格内容，请使用如下简单的方法 GROUP BY：

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

输出显示每年的总（合计）利润。要同时确定所有年份的总利润总和，您必须自己将各个值相加或运行额外的查询。或者您可以使用ROLLUP，它通过单个查询提供两个级别的分析。向子句添加 WITH ROLLUP修饰符GROUP BY会导致查询生成另一个（超级聚合）行，显示所有年份值的总计：

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
| NULL |   7535 |
+------+--------+

列中的NULL值year 标识总计超级聚合行。

ROLLUP当有多个GROUP BY列时，效果会更复杂。在这种情况下，每次除了最后一个分组列中的值发生变化时，查询都会生成一个额外的超级聚合摘要行。

例如，在没有的情况下，基于、和的表的摘要ROLLUP可能如下所示，其中输出仅指示年/国家/产品分析级别的摘要值： salesyearcountryproduct

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | Finland | Phone      |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
+------+---------+------------+--------+

添加后，ROLLUP查询会产生几个额外的行：

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | India   | NULL       |   1350 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2000 | USA     | NULL       |   1575 |
| 2000 | NULL    | NULL       |   4525 |
| 2001 | Finland | Phone      |     10 |
| 2001 | Finland | NULL       |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
| 2001 | USA     | NULL       |   3000 |
| 2001 | NULL    | NULL       |   3010 |
| NULL | NULL    | NULL       |   7535 |
+------+---------+------------+--------+

现在输出包括四个分析级别的摘要信息，而不仅仅是一个：

在给定年份和国家/地区的每组产品行之后，会出现一个额外的超级聚合摘要行，显示所有产品的总数。这些行的 product列设置为 NULL。
在给定年份的每组行之后，会出现一个额外的超级聚合摘要行，显示所有国家和产品的总数。这些行的 country和products 列设置为NULL。
最后，在所有其他行之后，会出现一个额外的超级聚合摘要行，显示所有年份、国家和产品的总计。此行的 year、country和 products列设置为 NULL。

每个超级聚合行中的NULL指标是在将行发送到客户端时生成的。服务器查看GROUP BY 最左边已更改值的子句中命名的列。对于名称与这些名称中的任何一个匹配的结果集中的任何列，其值都设置为NULL。（如果您指定按列位置对列进行分组，服务器会识别要按NULL位置设置的列。）

因为NULL超级聚合行中的值是在查询处理的后期放入结果集中的，所以您 NULL只能在选择列表或 HAVING子句中将它们作为值进行测试。您不能将它们作为 NULL连接条件或 WHERE子句中的值进行测试以确定要选择哪些行。例如，您不能添加WHERE product IS NULL到查询以从输出中消除除超级聚合行之外的所有行。

这些NULL值确实出现 NULL在客户端，并且可以使用任何 MySQL 客户端编程接口进行测试。但是此时，你无法区分a NULL代表的是常规分组值还是超聚合值。在MySQL 8.0中，可以使用 GROUPING()函数来测试区分。

使用 ROLLUP 时的其他注意事项

下面的讨论列出了一些特定于 MySQL 实现的行为ROLLUP。

使用时ROLLUP，不能同时使用 ORDER BY子句对结果进行排序。也就是说，ROLLUP和ORDER BY在MySQL中是互斥的。但是，您仍然可以控制排序顺序。要解决阻止使用ROLLUPwith 的限制ORDER BY并实现分组结果的特定排序顺序，请将分组结果集生成为派生表并应用于ORDER BY它。例如：

mysql> SELECT * FROM
         (SELECT year, SUM(profit) AS profit
         FROM sales GROUP BY year WITH ROLLUP) AS dt
       ORDER BY year DESC;
+------+--------+
| year | profit |
+------+--------+
| 2001 |   3010 |
| 2000 |   4525 |
| NULL |   7535 |
+------+--------+

在这种情况下，超级聚合汇总行按照计算它们的行进行排序，它们的位置取决于排序顺序（升序排序在开头，降序排序在结尾）。

LIMIT可用于限制返回给客户端的行数。LIMIT在之后应用ROLLUP，因此该限制适用于由添加的额外行ROLLUP。例如：

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP
       LIMIT 5;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
+------+---------+------------+--------+

使用LIMITwithROLLUP 可能会产生更难以解释的结果，因为用于理解超级聚合行的上下文较少。

MySQL 扩展允许未出现在 GROUP BY列表中的列在选择列表中被命名。（有关非聚合列和的信息 GROUP BY，请参阅第 12.20.3 节，“MySQL 对 GROUP BY 的处理”。）在这种情况下，服务器可以自由地从摘要行中的这个非聚合列中选择任何值，这包括添加的额外行通过 WITH ROLLUP。例如，在以下查询中，country是一个未出现在GROUP BY列表中的非聚合列，并且为此列选择的值是不确定的：

mysql> SELECT year, country, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India   |   4525 |
| 2001 | USA     |   3010 |
| NULL | USA     |   7535 |
+------+---------+--------+

ONLY_FULL_GROUP_BY当未启用 SQL 模式时，此行为是允许的。如果启用该模式，服务器将拒绝查询为非法，因为country未在GROUP BY子句中列出。ONLY_FULL_GROUP_BY启用后，您仍然可以使用非确定性值列的函数来执行查询 ANY_VALUE()：

mysql> SELECT year, ANY_VALUE(country) AS country, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India   |   4525 |
| 2001 | USA     |   3010 |
| NULL | USA     |   7535 |
+------+---------+--------+