• 首页
  • vue
  • TypeScript
  • JavaScript
  • scss
  • css3
  • html5
  • php
  • MySQL
  • redis
  • jQuery
  • GROUP BY 修饰符

    GROUP BY子句允许使用一个WITH ROLLUP修饰符,该修饰符使摘要输出包括代表更高级别(即超级聚合)摘要操作的额外行。ROLLUP因此,您可以通过一个查询在多个分析级别回答问题。例如,ROLLUP可用于提供对OLAP(在线分析处理)操作的支持。

    假设一个sales表有yearcountryproduct,和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 ROLLUPGROUP BY子句中添加修饰符会使查询产生另一行(超级汇总),该行显示所有年份值的总计:

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

    列中的NULLyear标识总计超级总计行。

    ROLLUP当有多GROUP BY列时,效果更复杂。在这种情况下,每当除最后一个分组列中的任何列中的值发生变化时,查询都会产生一个额外的超级汇总摘要行。

    例如,如果没有ROLLUP,在汇总sales表基础上yearcountryproduct可能是这样的,当输出只在一年/国家/产品的分析水平表明汇总值:

    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
    • 在给定年份的每组行之后,会出现一个额外的超级汇总行,显示所有国家和产品的总计。这些行的countryproducts列设置为NULL
    • 最后,在所有其他行之后,将出现一个额外的超级汇总摘要行,显示所有年份,国家和产品的总计。该行的yearcountryproducts列设置为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 BYWITH ROLLUP

    对于GROUP BY ... WITH ROLLUP查询,要测试NULL结果中的值是否表示超级集合值,GROUPING()可以在select list,HAVING子句和(自MySQL 8.0.12起)ORDER BY子句中使用该函数。例如,GROUPING(year)当返回1 NULLyear柱发生在超总量行,否则为0。类似地,GROUPING(country)GROUPING(product)返回1超总量NULL中的值countryproduct列,分别为:

    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()是0,如果任何一个表达式表示超总量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值,因此结果中的所有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 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 	|
    +-----------	+-----------	+----------	+
    

    使用ROLLUP时的其他注意事项

    以下讨论列出了特定于MySQL实现的一些行为ROLLUP

    在MySQL 8.0.12之前,使用时ROLLUP,您也不能使用ORDER BY子句对结果进行排序。换句话说,ROLLUP并且ORDER BY是在MySQL中相互排斥。但是,您仍然可以控制排序顺序。要解决阻止ROLLUPORDER BY分组结果一起使用并实现分组结果的特定排序顺序的限制,请生成分组结果集作为派生表并将其应用于表ORDER BY。例如:

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

    从MySQL 8.0.12开始,ORDER BY并且ROLLUP可以一起使用,这使得可以使用ORDER BYGROUPING()实现特定的分组结果排序顺序。例如:

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

    使用LIMITwith ROLLUP可能会产生更难以解释的结果,因为了解超级聚集行的上下文较少。

    MySQL扩展允许未出现在GROUP BY列表中的列在选择列表中被命名。(有关非聚合列的信息GROUP BY,请参见“ GROUP BY的MySQL处理”。)在这种情况下,服务器可以从摘要行中的该非聚合列中自由选择任何值,其中包括添加的额外行由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模式时,允许此行为。如果启用了该模式,则服务器会将该查询拒绝为非法查询,因为countryGROUP 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 	|
    +------	+---------	+--------	+