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 profitFROM salesGROUP BY year; +------ +-------- + | year | profit | +------ +-------- + | 2000 | 4525 | | 2001 | 3010 | +------ +-------- +
输出显示了每年的总(总计)利润。要确定所有年份的总利润,您必须自己累加各个值或运行其他查询。或者,您可以使用ROLLUP
,通过一个查询提供两个分析级别。WITH ROLLUP
在GROUP BY
子句中添加修饰符会使查询产生另一行(超级汇总),该行显示所有年份值的总计:
mysql>SELECT year, SUM(profit)AS profitFROM salesGROUP BY yearWITH ROLLUP ; +------ +-------- + | year | profit | +------ +-------- + | 2000 | 4525 | | 2001 | 3010 | | NULL | 7535 | +------ +-------- +
列中的NULL
值year
标识总计超级总计行。
ROLLUP
当有多GROUP BY
列时,效果更复杂。在这种情况下,每当除最后一个分组列中的任何列中的值发生变化时,查询都会产生一个额外的超级汇总摘要行。
例如,如果没有ROLLUP
,在汇总sales
表基础上year
,country
和product
可能是这样的,当输出只在一年/国家/产品的分析水平表明汇总值:
mysql>SELECT year, country, product, SUM(profit)AS profitFROM salesGROUP 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 profitFROM salesGROUP BY year, country, productWITH 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
超级聚合行中的值是在查询处理的最后阶段放入结果集中的,因此您只能将它们作为NULL
值在选择列表或HAVING
子句中进行测试。您不能将它们作为NULL
连接条件或WHERE
子句中的值进行测试来确定要选择的行。例如,您不能添加WHERE product IS NULL
到查询中以从输出中消除超级聚集行以外的所有行。
这些NULL
值的确显示NULL
在客户端,可以使用任何MySQL客户端编程接口进行测试。但是,在这一点上,您无法区分a NULL
代表常规分组值还是超聚合值。要测试区别,请使用GROUPING()
稍后描述的功能。
以前,MySQL不允许在具有选项的查询中使用DISTINCT
或。在MySQL 8.0.12及更高版本中取消了此限制。(错误#87450,错误#86311,错误#26640100,错误#26073513)ORDER BY
WITH ROLLUP
对于GROUP BY ... WITH ROLLUP
查询,要测试NULL
结果中的值是否表示超级集合值,GROUPING()
可以在select list,HAVING
子句和(自MySQL 8.0.12起)ORDER BY
子句中使用该函数。例如,GROUPING(year)
当返回1 NULL
在year
柱发生在超总量行,否则为0。类似地,GROUPING(country)
和GROUPING(product)
返回1超总量NULL
中的值country
和product
列,分别为:
mysql>SELECT year, country, product, SUM(profit)AS profit, GROUPING(year)AS grp_year, GROUPING(country)AS grp_country, GROUPING(product)AS grp_productFROM salesGROUP BY year, country, productWITH 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 profitFROM salesGROUP BY year, country, productWITH 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)
被评估为:
resultfor GROUPING(product) + resultfor GROUPING(country) << 1 + resultfor GROUPING(year) << 2
这样的结果GROUPING()
是0,如果任何一个表达式表示超总量NULL
,这样你就可以只返回超总量排和过滤出这样的常规分组行:
mysql>SELECT year, country, product, SUM(profit)AS profitFROM salesGROUP BY year, country, productWITH 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
值,因此结果中的所有NULL
值均ROLLUP
表示超级汇总值。当数据集包含NULL
值时,ROLLUP
摘要NULL
不仅可以在超级聚合行中包含值,而且可以在常规分组行中包含值。GROUPING()
使它们得以区分。假设该表t1
包含一个简单的数据集,该数据集具有一组数量值的两个分组因子,其中NULL
表示“ other ”或“ 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
值和常规分组行中的值:
mysql>SELECT name , size, SUM(quantity)AS quantityFROM t1GROUP BY name , sizeWITH 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 quantityFROM t1GROUP BY name , sizeWITH 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
。
在MySQL 8.0.12之前,使用时ROLLUP
,您也不能使用ORDER BY
子句对结果进行排序。换句话说,ROLLUP
并且ORDER BY
是在MySQL中相互排斥。但是,您仍然可以控制排序顺序。要解决阻止ROLLUP
与ORDER BY
分组结果一起使用并实现分组结果的特定排序顺序的限制,请生成分组结果集作为派生表并将其应用于表ORDER BY
。例如:
mysql>SELECT *FROM (SELECT year, SUM(profit)AS profitFROM salesGROUP BY yearWITH ROLLUP )AS dtORDER BY yearDESC ; +------ +-------- + | year | profit | +------ +-------- + | 2001 | 3010 | | 2000 | 4525 | | NULL | 7535 | +------ +-------- +
从MySQL 8.0.12开始,ORDER BY
并且ROLLUP
可以一起使用,这使得可以使用ORDER BY
和GROUPING()
实现特定的分组结果排序顺序。例如:
mysql>SELECT year, SUM(profit)AS profitFROM salesGROUP BY yearWITH 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 profitFROM salesGROUP BY year, country, productWITH 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
with ROLLUP
可能会产生更难以解释的结果,因为了解超级聚集行的上下文较少。
MySQL扩展允许未出现在GROUP BY
列表中的列在选择列表中被命名。(有关非聚合列的信息GROUP BY
,请参见“ GROUP BY的MySQL处理”。)在这种情况下,服务器可以从摘要行中的该非聚合列中自由选择任何值,其中包括添加的额外行由WITH ROLLUP
。例如,在以下查询中,country
是一个未聚合的列,该列未出现在GROUP BY
列表中,并且为此列选择的值是不确定的:
mysql>SELECT year, country, SUM(profit)AS profitFROM salesGROUP BY yearWITH 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 profitFROM salesGROUP BY yearWITH ROLLUP ; +------ +--------- +-------- + | year | country | profit | +------ +--------- +-------- + | 2000 | India | 4525 | | 2001 | USA | 3010 | | NULL | USA | 7535 | +------ +--------- +-------- +