MySQL 8.0 Release Notes
MySQL 8.0 Source Code Documentation
TheGROUP BYclause permits aWITH ROLLUPmodifier that causes summary output to include extra rows that represent higher-level (that is, super-aggregate) summary operations.ROLLUPthus enables you to answer questions at multiple levels of analysis with a single query. For example,ROLLUPcan be used to provide support for OLAP (Online Analytical Processing) operations.
Suppose that asalestable has一年,country,product, andprofitcolumns for recording sales profitability:
CREATE TABLE sales ( year INT, country VARCHAR(20), product VARCHAR(32), profit INT );To summarize table contents per year, use a simpleGROUP BYlike this:
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 useROLLUP, which provides both levels of analysis with a single query. Adding aWITH ROLLUPmodifier to theGROUP BYclause 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 | +------+--------+TheNULLvalue in the一年column identifies the grand total super-aggregate line.
ROLLUPhas a more complex effect when there are multipleGROUP BY列。在这种情况下,每次有一个变化in value in any but the last grouping column, the query produces an extra super-aggregate summary row.
为考试ple, withoutROLLUP, a summary of thesalestable based on一年,country, andproductmight look like this, where the output indicates summary values only at the year/country/product level of analysis:
mysql >选择一年,国家,产品,和(利润)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 | +------+---------+------------+--------+WithROLLUPadded, the query produces several extra rows:
mysql >选择一年,国家,产品,和(利润)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:
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
productcolumn set toNULL.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
countryandproductscolumns set toNULL.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
一年,country, andproductscolumns set toNULL.
TheNULLindicators in each super-aggregate row are produced when the row is sent to the client. The server looks at the columns named in theGROUP BYclause 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 toNULL. (If you specify grouping columns by column position, the server identifies which columns to set toNULLby position.)
Because theNULLvalues in the super-aggregate rows are placed into the result set at such a late stage in query processing, you can test them asNULLvalues only in the select list orHAVINGclause. You cannot test them asNULLvalues in join conditions or theWHERE条款来确定哪些行选择。为考试ple, you cannot addWHERE product IS NULLto the query to eliminate from the output all but the super-aggregate rows.
TheNULLvalues do appear asNULLon the client side and can be tested as such using any MySQL client programming interface. However, at this point, you cannot distinguish whether aNULLrepresents a regular grouped value or a super-aggregate value. To test the distinction, use theGROUPING()function, described later.
Previously, MySQL did not allow the use ofDISTINCTorORDER BYin a query having aWITH ROLLUPoption. This restriction is lifted in MySQL 8.0.12 and later. (Bug #87450, Bug #86311, Bug #26640100, Bug #26073513)
ForGROUP BY ... WITH ROLLUPqueries, to test whetherNULLvalues in the result represent super-aggregate values, theGROUPING()function is available for use in the select list,HAVINGclause, and (as of MySQL 8.0.12)ORDER BYclause. For example,GROUPING(year)returns 1 whenNULLin the一年column occurs in a super-aggregate row, and 0 otherwise. Similarly,GROUPING(country)andGROUPING(product)return 1 for super-aggregateNULLvalues in thecountryandproductcolumns, respectively:
mysql >选择一年,国家,产品,和(利润)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 theGROUPING()results directly, you can useGROUPING()to substitute labels for super-aggregateNULLvalues:
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. For example,GROUPING(year, country, product)is evaluated like this:
result for GROUPING(product) + result for GROUPING(country) << 1 + result for GROUPING(一年) << 2The result of such aGROUPING()is nonzero if any of the expressions represents a super-aggregateNULL, so you can return only the super-aggregate rows and filter out the regular grouped rows like this:
mysql >选择一年,国家,产品,和(利润)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 | +------+---------+---------+--------+Thesalestable contains noNULLvalues, so allNULLvalues in aROLLUPresult represent super-aggregate values. When the data set containsNULLvalues,ROLLUPsummaries may containNULLvalues not only in super-aggregate rows, but also in regular grouped rows.GROUPING()enables these to be distinguished. Suppose that tablet1contains a simple data set with two grouping factors for a set of quantity values, whereNULLindicates 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 | +------+-------+----------+A simpleROLLUPoperation produces these results, in which it is not so easy to distinguishNULLvalues in super-aggregate rows fromNULLvalues 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 | +------+-------+----------+UsingGROUPING()to substitute labels for the super-aggregateNULLvalues 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 | +-----------+-----------+----------+The following discussion lists some behaviors specific to the MySQL implementation ofROLLUP.
Prior to MySQL 8.0.12, when you useROLLUP, you cannot also use anORDER BYclause to sort the results. In other words,ROLLUPandORDER BYwere mutually exclusive in MySQL. However, you still have some control over sort order. To work around the restriction that prevents usingROLLUP与ORDER BYand achieve a specific sort order of grouped results, generate the grouped result set as a derived table and applyORDER BYto it. 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 BYandROLLUPcan be used together, which enables the use ofORDER BYandGROUPING()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).
LIMITcan be used to restrict the number of rows returned to the client.LIMITis applied afterROLLUP, so the limit applies against the extra rows added byROLLUP. For example:
mysql >选择一年,国家,产品,和(利润)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 | +------+---------+------------+--------+UsingLIMIT与ROLLUPmay produce results that are more difficult to interpret, because there is less context for understanding the super-aggregate rows.
A MySQL extension permits a column that does not appear in theGROUP BYlist to be named in the select list. (For information about nonaggregated columns andGROUP BY, seeSection 12.20.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 byWITH ROLLUP. For example, in the following query,countryis a nonaggregated column that does not appear in theGROUP BYlist 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 | +------+---------+--------+This behavior is permitted when theONLY_FULL_GROUP_BYSQL mode is not enabled. If that mode is enabled, the server rejects the query as illegal becausecountryis not listed in theGROUP BYclause. WithONLY_FULL_GROUP_BYenabled, you can still execute the query by using theANY_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 | +------+---------+--------+