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

MySQL 8.4 参考手册 · 中文镜像

正文为中文，英文原文默认隐藏。点击任意中文段落即可展开 / 收起对应英文原文。

译文由机器翻译生成，仅供理解参考；有疑问请以官方英文原文为准。

MySQL 8.4 参考手册 / 14 函数和运算符 / 14.19 聚合函数 / 14.19.2 GROUP BY 修饰符

中文点击中文段落可显示 / 隐藏英文原文本页译文覆盖 100%

14.19.2 GROUP BY 修饰符14.19.2 GROUP BY Modifiers

GROUP BY 子句允许使用 WITH ROLLUP 修饰符，该修饰符会导致汇总输出包含表示更高级别（即超级汇总）操作的额外行。因此，ROLLUP 使您能够通过单个查询回答多层次分析的问题。例如，ROLLUP 可用于为 OLAP（联机分析处理）操作提供支持。The GROUP BY clause permits a WITH ROLLUP modifier that causes summary output to include extra rows that represent higher-level (that is, super-aggregate) summary operations. ROLLUP thus enables you to answer questions at multiple levels of analysis with a single query. For example, ROLLUP can be used to provide support for OLAP (Online Analytical Processing) operations.

假设有一个 sales 表，其中包含 year、country、product 和 profit 列来记录销售利润：Suppose that a sales table has year, country, product, and profit columns for recording sales profitability:

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

要按年份汇总表内容，可使用如下简单的 GROUP BY：To summarize table contents per year, use a simple GROUP BY like this:

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

输出显示每年的总（汇总）利润。要同时确定所有年份的总利润，您必须自己将各个值相加，或者运行另一个查询。或者，您可以使用 ROLLUP，它可以通过单个查询提供两种级别的分析。在 WITH ROLLUP 子句中添加 GROUP BY 修饰符，将使查询生成另一行（超级汇总行），显示所有年份值的总计：The output shows the total (aggregate) profit for each year. To also determine the total profit summed over all years, you must add up the individual values yourself or run an additional query. Or you can use ROLLUP, which provides both levels of analysis with a single query. Adding a WITH ROLLUP modifier to the GROUP BY clause causes the query to produce another (super-aggregate) row that shows the grand total over all year values:

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

year 列中的 NULL 值标识总计超级汇总行。The NULL value in the year column identifies the grand total super-aggregate line.

MySQL 支持此修饰符的另一种替代语法，如第 15.2.13 节，“SELECT 语句” 所示。使用替代语法，可以像下面这样执行前面的查询：MySQL supports an additional, alternative syntax for this modifier, as shown in Section 15.2.13, “SELECT Statement”. Using the alternative syntax, the previous query can be performed as shown here:

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

ROLLUP 当存在多个 GROUP BY 列时，效果会更复杂。在这种情况下，每当除最后一列分组列以外的任意列的值发生变化时，查询都会生成一个额外的超级汇总行。ROLLUP has a more complex effect when there are multiple GROUP BY columns. In this case, each time there is a change in value in any but the last grouping column, the query produces an extra super-aggregate summary row.

例如，如果没有 ROLLUP，基于 year、country 和 product 对 sales 表进行汇总可能如下所示，输出仅显示年/国家/产品级别的汇总值：For example, without ROLLUP, a summary of the sales table based on year, country, and product might look like this, where the output indicates summary values only at the year/country/product level of analysis:

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 后，查询会生成几个额外的行：With ROLLUP added, the query produces several extra rows:

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

现在输出包括四个层次的分析总结信息，而不仅仅是一个：Now the output includes summary information at four levels of analysis, not just one:

在每个特定年份和国家的产品行集之后，会出现一个额外的超级汇总行，显示所有产品的总计。这些行的 product 列设置为 NULL。Following each set of product rows for a given year and country, an extra super-aggregate summary row appears showing the total for all products. These rows have the product column set to NULL.
在每个特定年份的行集之后，会出现一个额外的超级汇总行，显示所有国家和产品的总计。这些行的 country 和 products 列设置为 NULL。Following each set of rows for a given year, an extra super-aggregate summary row appears showing the total for all countries and products. These rows have the country and products columns set to NULL.
最后，在所有其他行之后，会出现一个额外的超级汇总行，显示所有年份、国家和产品的总计。这行的 year、country 和 products 列设置为 NULL。Finally, following all other rows, an extra super-aggregate summary row appears showing the grand total for all years, countries, and products. This row has the year, country, and products columns set to NULL.

当每个超级聚合行被发送到客户端时，会生成 NULL 指示符。服务器查看 GROUP BY 子句中紧随第一个值已变化的列之后的列。对于结果集中名称与这些名称匹配的任何列，其值将被设置为 NULL。（如果您通过列位置指定分组列，服务器会根据位置确定要设置为 NULL 的列。）The NULL indicators in each super-aggregate row are produced when the row is sent to the client. The server looks at the columns named in the GROUP BY clause following the leftmost one that has changed value. For any column in the result set with a name that matches any of those names, its value is set to NULL. (If you specify grouping columns by column position, the server identifies which columns to set to NULL by position.)

由于超级聚合行中的 NULL 值是在查询处理的很晚阶段才放入结果集中，因此您只能在选择列表或 HAVING 子句中将它们作为 NULL 值进行测试。您不能在连接条件或 WHERE 子句中将它们作为 NULL 值来确定要选择哪些行。例如，您不能在查询中添加 WHERE product IS NULL 来从输出中删除除超级聚合行之外的所有行。Because the NULL values in the super-aggregate rows are placed into the result set at such a late stage in query processing, you can test them as NULL values only in the select list or HAVING clause. You cannot test them as NULL values in join conditions or the WHERE clause to determine which rows to select. For example, you cannot add WHERE product IS NULL to the query to eliminate from the output all but the super-aggregate rows.

在客户端，NULL 值确实会显示为 NULL，并且可以使用任何 MySQL 客户端编程接口进行测试。然而，在这一点上，你无法区分一个 NULL 是表示常规分组值还是超级聚合值。要测试这种区别，请使用稍后介绍的 GROUPING() 函数。The NULL values do appear as NULL on the client side and can be tested as such using any MySQL client programming interface. However, at this point, you cannot distinguish whether a NULL represents a regular grouped value or a super-aggregate value. To test the distinction, use the GROUPING() function, described later.

对于 GROUP BY ... WITH ROLLUP 查询，要测试结果中的 NULL 值是否表示超汇总值，可以在 SELECT 列表、HAVING 子句和 ORDER BY 子句中使用 GROUPING() 函数。例如，当 year 列中的 NULL 出现在超汇总行时，GROUPING(year) 返回 1，否则返回 0。同样地，GROUPING(country) 和 GROUPING(product) 分别在 country 和 product 列的超汇总 NULL 值时返回 1：For GROUP BY ... WITH ROLLUP queries, to test whether NULL values in the result represent super-aggregate values, the GROUPING() function is available for use in the select list, HAVING clause, and ORDER BY clause. For example, GROUPING(year) returns 1 when NULL in the year column occurs in a super-aggregate row, and 0 otherwise. Similarly, GROUPING(country) and GROUPING(product) return 1 for super-aggregate NULL values in the country and product columns, respectively:

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

使用前面提到的替代语法，这个查询可以这样重写：Using the alternative syntax mentioned previously, this query can be rewritten like this:

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 ROLLUP (year, country, product);

您可以使用 GROUPING() 来替代超级聚合的 NULL 值的标签，而不是直接显示 GROUPING() 结果：Instead of displaying the GROUPING() results directly, you can use GROUPING() to substitute labels for super-aggregate NULL values:

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) 的计算方式如下：With multiple expression arguments, GROUPING() returns a result representing a bitmask that combines the results for each expression, with the lowest-order bit corresponding to the result for the rightmost expression. For example, GROUPING(year, country, product) is evaluated like this:

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

如果任何表达式表示一个超级聚合 NULL，则这样的 GROUPING() 的结果非零，因此您可以只返回超级聚合行，并像这样过滤掉常规分组行：The result of such a GROUPING() is nonzero if any of the expressions represents a super-aggregate NULL, so you can return only the super-aggregate rows and filter out the regular grouped rows like this:

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 表示类似 “其他” 或 “未知” 的情况：The sales table contains no NULL values, so all NULL values in a ROLLUP result represent super-aggregate values. When the data set contains NULL values, ROLLUP summaries may contain NULL values not only in super-aggregate rows, but also in regular grouped rows. GROUPING() enables these to be distinguished. Suppose that table t1 contains a simple data set with two grouping factors for a set of quantity values, where NULL indicates something like “other” or “unknown”:

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 值：A simple ROLLUP operation produces these results, in which it is not so easy to distinguish NULL values in super-aggregate rows from NULL values in regular grouped rows:

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 值的标签，使结果更容易理解：Using GROUPING() to substitute labels for the super-aggregate NULL values makes the result easier to interpret:

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 时的其他注意事项Other Considerations When using ROLLUP

下面的讨论列出了一些 MySQL 实现 ROLLUP 的特定行为。The following discussion lists some behaviors specific to the MySQL implementation of ROLLUP.

ORDER BY 和 ROLLUP 可以一起使用，这使得可以使用 ORDER BY 和 GROUPING() 来实现分组结果的特定排序。例如：ORDER BY and ROLLUP can be used together, which enables the use of ORDER BY and GROUPING() to achieve a specific sort order of grouped results. For example:

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

在这两种情况下，超汇总摘要行与其计算来源的行一起排序，其位置取决于排序顺序（升序时在末尾，降序时在开头）。In both cases, the super-aggregate summary rows sort with the rows from which they are calculated, and their placement depends on sort order (at the end for ascending sort, at the beginning for descending sort).

LIMIT 可以用来限制返回给客户端的行数。LIMIT 在 ROLLUP 之后应用，因此限制适用于 ROLLUP 添加的额外行。例如：LIMIT can be used to restrict the number of rows returned to the client. LIMIT is applied after ROLLUP, so the limit applies against the extra rows added by ROLLUP. For example:

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

在 ROLLUP 中使用 LIMIT 可能会产生更难以解释的结果，因为理解超级汇总行的上下文较少。Using LIMIT with ROLLUP may produce results that are more difficult to interpret, because there is less context for understanding the super-aggregate rows.

MySQL 的一个扩展允许在 select 列表中使用未出现在 GROUP BY 列表中的列。（关于非聚合列和 GROUP BY 的信息，请参见第 14.19.3 节，“MySQL 对 GROUP BY 的处理”。）在这种情况下，服务器可以自由选择摘要行中该非聚合列的任意值，这也包括由 WITH ROLLUP 添加的额外行。例如，在以下查询中，country 是一个未聚合的列，它没有出现在 GROUP BY 列表中，并且为此列选择的值是不可确定的：A MySQL extension permits a column that does not appear in the GROUP BY list to be named in the select list. (For information about nonaggregated columns and GROUP BY, see Section 14.19.3, “MySQL Handling of GROUP BY”.) In this case, the server is free to choose any value from this nonaggregated column in summary rows, and this includes the extra rows added by WITH ROLLUP. For example, in the following query, country is a nonaggregated column that does not appear in the GROUP BY list and values chosen for this column are nondeterministic:

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() 函数来执行该查询：This behavior is permitted when the ONLY_FULL_GROUP_BY SQL mode is not enabled. If that mode is enabled, the server rejects the query as illegal because country is not listed in the GROUP BY clause. With ONLY_FULL_GROUP_BY enabled, you can still execute the query by using the ANY_VALUE() function for nondeterministic-value columns:

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 节，“全文搜索函数”。A rollup column cannot be used as an argument to MATCH() (and is rejected with an error) except when called in a WHERE clause. See Section 14.9, “Full-Text Search Functions”, for more information.

首页上一级下一节