MySQL :: MySQL 8.3 Reference Manual :: 14.19.2 GROUP BY Modifiers

version 8.3

8.0 current
5.7

8.0 Japanese

MySQL 8.3 Reference Manual / ... / GROUP BY Modifiers

14.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，它使用单个查询提供两个级别的分析。

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

添加 WITH ROLLUP 修饰符到 GROUP BY 子句将导致查询生成另一个（超聚合）行，该行显示所有年份的总利润：

year 列中的 NULL 值标识总利润超聚合行。

ROLLUP 在存在多个 GROUP BY 列时具有更复杂的效果。在这种情况下，每当任何但最后一个分组列的值发生变化时，查询将生成额外的超聚合摘要行。

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 |
+------+---------+------------+--------+

例如，without ROLLUP，sales 表的摘要基于 year、country 和 product 可能如下所示，其中输出仅指示年/国家/产品级别的摘要值：

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 |
+------+---------+------------+--------+

使用 ROLLUP 添加后，查询生成多个额外的行：

现在输出包括四个级别的摘要信息，而不仅仅是一个级别：
在每个产品行集之后，为给定年份和国家，出现一个额外的超聚合摘要行，显示所有产品的总利润。这些行的 product 列设置为 NULL。
在每个国家行集之后，为给定年份，出现一个额外的超聚合摘要行，显示所有国家和产品的总利润。这些行的 country 和 product 列设置为 NULL。

最后，在所有其他行之后，出现一个额外的超聚合摘要行，显示所有年份、国家和产品的总利润。该行的 year、country 和 product 列设置为 NULL。

在每个超聚合行中的 NULL 指示符是在客户端生成的。当服务器查看 GROUP BY 子句中指定的列时，它会将结果集中的相应列设置为 NULL。（如果您按列位置指定分组列，服务器将根据位置确定哪些列设置为 NULL。）

因为超聚合行中的 NULL 值是在查询处理的晚期阶段生成的，因此您只能在选择列表或 HAVING 子句中将其测试为 NULL 值。您不能在连接条件或 WHERE 子句中将其测试为 NULL 值，以确定要选择的行。例如，您不能添加 WHERE product IS NULL 以从输出中删除所有但超聚合行。

对于 GROUP BY ... WITH ROLLUP 查询，为了测试结果中的 NULL 值是否表示超聚合值，可以在选择列表、HAVING 子句和 ORDER BY 子句中使用 GROUPING() 函数。例如，GROUPING(year) 在超聚合行中返回 1，当 year 列中的 NULL 值出现时，否则返回 0。同样，GROUPING(country) 和 GROUPING(product) 分别返回超聚合 NULL 值在 country 和 product 列中的 1：

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

而不是直接显示 GROUPING() 结果，可以使用 GROUPING() 将超聚合 NULL 值替换为标签：

mysql> SELECT
         IF(GROUPING(year), 'All years', year) AS year,
         IF(GROUPING(country), 'All countries', country) AS country,
         IF(GROUPING(product), 'All products', product) AS 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       | All products |   1600 |
| 2000      | India         | Calculator   |    150 |
| 2000      | India         | Computer     |   1200 |
| 2000      | India         | All products |   1350 |
| 2000      | USA           | Calculator   |     75 |
| 2000      | USA           | Computer     |   1500 |
| 2000      | USA           | All products |   1575 |
| 2000      | All countries | All products |   4525 |
| 2001      | Finland       | Phone        |     10 |
| 2001      | Finland       | All products |     10 |
| 2001      | USA           | Calculator   |     50 |
| 2001      | USA           | Computer     |   2700 |
| 2001      | USA           | TV           |    250 |
| 2001      | USA           | All products |   3000 |
| 2001      | All countries | All products |   3010 |
| All years | All countries | All products |   7535 |
+-----------+---------------+--------------+--------+

具有多个表达式参数时，GROUPING() 返回一个结果，表示每个表达式的结果的位掩码，右侧表达式对应最低位。例如，GROUPING(year, country, product) 评估如下：

  result for GROUPING(product)
+ result for GROUPING(country) << 1
+ result for GROUPING(year) << 2

这样的 GROUPING() 结果非零，如果任何表达式表示超聚合 NULL，因此可以返回超聚合行并过滤出常规分组行，如下所示：

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP
       HAVING GROUPING(year, country, product) <> 0;
+------+---------+---------+--------+
| year | country | product | profit |
+------+---------+---------+--------+
| 2000 | Finland | NULL    |   1600 |
| 2000 | India   | NULL    |   1350 |
| 2000 | USA     | NULL    |   1575 |
| 2000 | NULL    | NULL    |   4525 |
| 2001 | Finland | NULL    |     10 |
| 2001 | USA     | NULL    |   3000 |
| 2001 | NULL    | NULL    |   3010 |
| NULL | NULL    | NULL    |   7535 |
+------+---------+---------+--------+

表 sales 不包含 NULL 值，因此结果中的所有 NULL 值都表示超聚合值。当数据集包含 NULL 值时，ROLLUP 汇总可能包含不仅在超聚合行中，也在常规分组行中的 NULL 值。GROUPING() 可以区分这些值。假设表 t1 包含一个简单的数据集，具有两个分组因子，用于一组数量值，其中 NULL 表示类似于“其他”或“未知”的值：

mysql> SELECT * FROM t1;
+------+-------+----------+
| name | size  | quantity |
+------+-------+----------+
| ball | small |       10 |
| ball | large |       20 |
| ball | NULL  |        5 |
| hoop | small |       15 |
| hoop | large |        5 |
| hoop | NULL  |        3 |
+------+-------+----------+

一个简单的 ROLLUP 操作将产生以下结果，其中很难区分超聚合行中的 NULL 值和常规分组行中的 NULL 值：

mysql> SELECT name, size, SUM(quantity) AS quantity
       FROM t1
       GROUP BY name, size WITH ROLLUP;
+------+-------+----------+
| name | size  | quantity |
+------+-------+----------+
| ball | NULL  |        5 |
| ball | large |       20 |
| ball | small |       10 |
| ball | NULL  |       35 |
| hoop | NULL  |        3 |
| hoop | large |        5 |
| hoop | small |       15 |
| hoop | NULL  |       23 |
| NULL | NULL  |       58 |
+------+-------+----------+

使用 GROUPING() 将超聚合 NULL 值替换为标签，使结果更易于解释：

mysql> SELECT
         IF(GROUPING(name) = 1, 'All items', name) AS name,
         IF(GROUPING(size) = 1, 'All sizes', size) AS size,
         SUM(quantity) AS quantity
       FROM t1
       GROUP BY name, size WITH ROLLUP;
+-----------+-----------+----------+
| name      | size      | quantity |
+-----------+-----------+----------+
| ball      | NULL      |        5 |
| ball      | large     |       20 |
| ball      | small     |       10 |
| ball      | All sizes |       35 |
| hoop      | NULL      |        3 |
| hoop      | large     |        5 |
| hoop      | small     |       15 |
| hoop      | All sizes |       23 |
| All items | All sizes |       58 |
+-----------+-----------+----------+

使用 ROLLUP 时的其他考虑因素

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

ORDER BY 和 ROLLUP 可以一起使用，这使得可以使用 ORDER BY 和 GROUPING() 实现特定的分组结果排序顺序。例如：

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

在这两种情况下，超聚合汇总行都与其计算的行一起排序，排序顺序决定了它们的位置（升序排序时在末尾，降序排序时在开头）。

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 |
+------+---------+------------+--------+

使用 LIMIT 和 ROLLUP 可能会产生难以解释的结果，因为超聚合行的上下文较少。

MySQL 扩展允许在 SELECT 列表中命名不在 GROUP BY 列表中的列。(关于非聚合列和 GROUP BY 的信息，请参阅第 14.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 子句中。启用 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 |
+------+---------+--------+

汇总列不能用作 MATCH() 的参数（并将其拒绝并显示错误），除非在 WHERE 子句中调用。请参阅第 14.9 节，“全文搜索函数”，以获取更多信息。

PREV HOME UP NEXT