MySQL :: MySQL 9.0 参考手册 :: 14.19.2 GROUP BY 修饰符

版本 9.0

8.4 当前
8.0
5.7

8.0 日语

MySQL 9.0 参考手册 / ... / GROUP BY 修饰符

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，它通过单个查询提供两种级别的分析。向 GROUP BY 子句添加 WITH ROLLUP 修饰符会导致查询生成另一行（超级聚合行），显示所有年份值的总计。

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

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

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

例如，如果没有 ROLLUP，则基于 year、country 和 product 的 sales 表汇总可能如下所示，其中输出仅指示年份/国家/产品分析级别的汇总值。

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 值是在查询处理的后期阶段放入结果集的，所以您只能在选择列表或 HAVING 子句中将它们测试为 NULL 值。您不能在连接条件或 WHERE 子句中将它们测试为 NULL 值以确定要选择的行。例如，您不能向查询中添加 WHERE product IS NULL 以从输出中排除除超级聚合行之外的所有行。

NULL 值在客户端确实显示为 NULL，并且可以使用任何 MySQL 客户端编程接口将其测试为 NULL。但是，此时，您无法区分 NULL 表示常规分组值还是超级聚合值。要测试区别，请使用稍后介绍的 GROUPING() 函数。

对于 GROUP BY ... WITH ROLLUP 查询，要测试结果中的 NULL 值是否表示超级聚合值，GROUPING() 函数可用于选择列表、HAVING 子句和 ORDER BY 子句。例如，当 year 列中的 NULL 出现在超级聚合行中时，GROUPING(year) 返回 1，否则返回 0。类似地，对于 country 和 product 列中的超级聚合 NULL 值，GROUPING(country) 和 GROUPING(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() 为超级聚合 NULL 值替换标签，而不是直接显示 GROUPING() 结果。

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

如果任何表达式表示超级聚合 NULL，则此类 GROUPING() 的结果为非零值，因此您可以仅返回超级聚合行并按如下方式过滤掉常规分组的行

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 值，因此 ROLLUP 结果中的所有 NULL 值都表示超级聚合值。当数据集包含 NULL 值时，ROLLUP 汇总可能不仅在超级聚合行中包含 NULL 值，而且在常规分组行中也包含 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 扩展允许在选择列表中命名 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 |
+------+---------+--------+

除了在 WHERE 子句中调用外，汇总列不能用作 MATCH() 的参数（并且会被拒绝并报错）。有关详细信息，请参阅第 14.9 节“全文搜索函数”。