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

version 8.4

8.0 current
5.7

8.0 Japanese

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

14.19.2 GROUP BY 修饰符

GROUP BY take clause 允许添加一个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 |
+------+--------+

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

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

例如，without ROLLUP，基于 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子句中测试它们以确定要选择的行。例如，您不能添加WHERE product IS NULL到查询中，以删除所有超级聚合行以外的输出。

这些NULL值在客户端上确实会显示为NULL，并且可以使用任何MySQL客户端编程接口来测试它们。然而，在这个阶段，您不能区分一个NULL是否表示常规组合值或超级聚合值。要测试区别，请使用GROUPING()函数，后面将有描述。

对于使用GROUP BY ... WITH ROLLUP查询的结果，测试结果中是否包含超聚合值，可以使用GROUPING()函数在选择列表、HAVING子句和ORDER BY子句中。例如，GROUPING(year)将在超聚合行中的year列出现NULL值时返回1，否则返回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值，所以在ROLLUP结果中的所有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 |
+-----------+-----------+----------+

以下讨论列举了 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 |
+------+---------+--------+

rollup 列不能用作 MATCH() 的参数（并且会被拒绝）except 在 WHERE 子句中被调用时。见第14.9节，“全文搜索函数”，了解更多信息。

PREV HOME UP NEXT