12.19.2 GROUP BY 修饰符_MySQL 5.6 参考手册

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

12.19.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代表的是常规分组值还是超聚合值。

使用 ROLLUP 时的其他注意事项

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

使用时ROLLUP，不能同时使用 ORDER BY子句对结果进行排序。也就是说，ROLLUP和ORDER BY在MySQL中是互斥的。但是，您仍然可以控制排序顺序。GROUP BY在 MySQL 中，默认情况下对结果进行隐式排序（在没有ASC或 DESC指示符的情况下）。但是，不推荐使用 MySQL 中的隐式 GROUP BY排序。要实现分组结果的特定排序顺序：

将显式ASC和 DESC关键字与列表中命名的 GROUP BY列一起使用，以指定各个列的排序顺序。在这种情况下，无论排序顺序如何，添加的超级聚合摘要行ROLLUP仍然出现在计算它们的行之后。
要解决阻止使用 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.19.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子句中列出。