12.20.2 GROUP BY ModifiersGROUP BY修饰符

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. GROUP BY子句允许WITH ROLLUP修饰符使摘要输出包含表示更高级别（即超级聚合）摘要操作的额外行。ROLLUP thus enables you to answer questions at multiple levels of analysis with a single query. 因此，ROLLUP使您能够用一个查询在多个分析级别上回答问题。For example, ROLLUP can be used to provide support for OLAP (Online Analytical Processing) operations.例如，ROLLUP可用于支持OLAP（联机分析处理）操作。

Suppose that a sales table has year, country, product, and profit columns for recording sales profitability:假设sales表有year、country、product和profit列用于记录销售盈利能力：

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

To summarize table contents per year, use a simple GROUP BY like this:要总结每年的表格内容，请使用以下简单GROUP BY：

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

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. 或者可以使用ROLLUP，它通过一个查询提供两个级别的分析。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:将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 |
+------+--------+

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

ROLLUP has a more complex effect when there are multiple GROUP BY columns. 当有多个GROUP BY列时，ROLLUP的效果更为复杂。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.在这种情况下，每次除最后一个分组列外的任何列中的值发生更改时，查询都会生成一个额外的超级聚合摘要行。

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:例如，如果不使用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 |
+------+---------+------------+--------+

With ROLLUP added, the query produces several extra rows:添加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 |
+------+---------+------------+--------+

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

The NULL indicators in each super-aggregate row are produced when the row is sent to the client. 每个超级聚合行中的NULL指示符是在将该行发送到客户端时生成的。The server looks at the columns named in the GROUP BY clause following the leftmost one that has changed value. 服务器将查看GROUP BY子句中的列，这些列位于值已更改的最左侧列之后。For any column in the result set with a name that matches any of those names, its value is set to NULL. 对于结果集中名称与这些名称匹配的任何列，其值设置为NULL。(If you specify grouping columns by column position, the server identifies which columns to set to NULL by position.)（如果指定按列位置对列进行分组，则服务器将按位置标识要设置为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. 由于超级聚合行中的NULL值是在查询处理的后期阶段放入结果集中的，因此只能在SELECT列表或HAVING子句中将它们作为空值进行测试。You cannot test them as NULL values in join conditions or the WHERE clause to determine which rows to select. 不能在连接条件或WHERE子句中将它们作为NULL值进行测试，以确定要选择的行。For example, you cannot add WHERE product IS NULL to the query to eliminate from the output all but the super-aggregate rows.例如，不能将WHERE product IS NULL添加到查询中，以从输出中除去除超级聚合行以外的所有行。

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

Previously, MySQL did not allow the use of DISTINCT or ORDER BY in a query having a WITH ROLLUP option. 以前，MySQL不允许在带有WITH ROLLUP选项的查询中使用DISTINCT或ORDER BY。This restriction is lifted in MySQL 8.0.12 and later. MySQL8.0.12及更高版本中取消了此限制。(Bug #87450, Bug #86311, Bug #26640100, Bug #26073513)

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 (as of MySQL 8.0.12) ORDER BY clause. 对于GROUP BY ... WITH ROLLUP查询，要测试结果中的NULL值是否表示超级聚合值，GROUPING()函数可用于SELECT列表、HAVING子句和（从MySQL 8.0.12开始）ORDER BY子句。For example, GROUPING(year) returns 1 when NULL in the year column occurs in a super-aggregate row, and 0 otherwise. 例如，当超级聚合行中的year列为NULL时，GROUPING(year)返回1，否则返回0。Similarly, GROUPING(country) and GROUPING(product) return 1 for super-aggregate NULL values in the country and product columns, respectively:类似地，GROUPING(country)和GROUPING(product)分别为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 |
+------+---------+------------+--------+----------+-------------+-------------+

Instead of displaying the GROUPING() results directly, you can use GROUPING() to substitute labels for super-aggregate NULL values:不必直接显示GROUPING()结果，您可以使用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 |
+-----------+---------------+--------------+--------+

With multiple expression arguments, GROUPING() returns a result representing a bitmask the combines the results for each expression, with the lowest-order bit corresponding to the result for the rightmost expression. 对于多个表达式参数，GROUPING()返回一个表示位掩码的结果，该位掩码将合并每个表达式的结果，最低阶位对应于最右边表达式的结果。For example, GROUPING(year, country, product) is evaluated like this:例如，GROUPING(year, country, product)的评估方式如下：

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

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:如果任何表达式表示超级聚合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 |
+------+---------+---------+--------+

The sales table contains no NULL values, so all NULL values in a ROLLUP result represent super-aggregate values. sales表不包含NULL值，因此汇总结果中的所有NULL值都表示超级聚合值。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. 当数据集包含空值时，ROLLUP摘要不仅可以在超级聚合行中包含NULL值，还可以在常规分组行中包含空值。GROUPING() enables these to be distinguished. GROUPING()允许区分这些。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”:假设表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 |
+------+-------+----------+

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:简单的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 |
+------+-------+----------+

Using GROUPING() to substitute labels for the super-aggregate NULL values makes the result easier to interpret:使用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 |
+-----------+-----------+----------+

Other Considerations When using ROLLUP使用ROLLUP时的其他注意事项

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

Prior to MySQL 8.0.12, when you use ROLLUP, you cannot also use an ORDER BY clause to sort the results. 在MySQL8.0.12之前，当您使用ROLLUP时，也不能使用ORDER BY子句对结果进行排序。In other words, ROLLUP and ORDER BY were mutually exclusive in MySQL. 换句话说，ROLLUP和ORDER BY在MySQL中是互斥的。However, you still have some control over sort order. 但是，您仍然可以控制排序顺序。To work around the restriction that prevents using ROLLUP with ORDER BY and achieve a specific sort order of grouped results, generate the grouped result set as a derived table and apply ORDER BY to it. 若要解决阻止将ROLLUP与ORDER BY一起使用的限制，并实现分组结果的特定排序顺序，请将分组结果集生成为派生表并对其应用ORDER BY。For example:

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

As of MySQL 8.0.12, 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. 从MySQL 8.0.12开始，ORDER BY和ROLLUP可以一起使用，这使得ORDER BY和GROUPING()能够实现分组结果的特定排序顺序。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 can be used to restrict the number of rows returned to the client. LIMIT可用于限制返回给客户机的行数。LIMIT is applied after ROLLUP, so the limit applies against the extra rows added by ROLLUP. LIMIT在ROLLUP之后应用，因此LIMIT应用于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 |
+------+---------+------------+--------+

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

A MySQL extension permits a column that does not appear in the GROUP BY list to be named in the select list. MySQL扩展允许在选择列表中命名GROUP BY列表中未出现的列。(For information about nonaggregated columns and GROUP BY, see Section 12.20.3, “MySQL Handling of GROUP BY”.) （有关非聚集列和GROUP BY的信息，请参阅第12.20.3节，“MySQL处理分组方式”。）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. 在这种情况下，服务器可以自由地从摘要行中的未聚合列中选择任何值，这包括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:例如，在下面的查询中，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 |
+------+---------+--------+

This behavior is permitted when the ONLY_FULL_GROUP_BY SQL mode is not enabled. 如果未启用ONLY_FULL_GROUP_BYSQL模式，则允许此行为。If that mode is enabled, the server rejects the query as illegal because country is not listed in the GROUP BY clause. 如果启用了该模式，服务器将拒绝该查询，因为GROUP BY子句中没有列出country。With ONLY_FULL_GROUP_BY enabled, you can still execute the query by using the ANY_VALUE() function for nondeterministic-value columns:在启用了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 |
+------+---------+--------+