GROUP BY Modifiers修饰符

WITH ROLLUP

官方：http://dev.mysql.com/doc/refman/5.7/en/group-by-modifiers.html

WITH ROLLUP修饰符会输出另外的汇总行，实现更高级的汇总操作。官网下面继续描述它的好处，此处忽略。

进入exp：

首先创建一张销售表：

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

根据年份做一个关于利润的简单汇总：

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

上面是根据每年计算的利润，若想计算所有年份的总利润，你必须自己加上每年的利润，或者作另外的查询。这时WITH ROLLUP闪亮登场，它会多输出所有年份的总利润，即提供牛叉的多层次分析（both levels of analysis with a single query）：

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

上面汇总时，总利润的年份会被置为NULL。

 

官方最下面有一个用户评论：

改造上面语句，修改NULL为Total，贴过来：

Even though the NULL rows are inserted late in query processing, they seem to be available to the select_expr part of a SELECT statement. Using the example table:
SELECT IFNULL(year,"Total") as year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP（也可以用 COALESCE(year,"Total")代替IFNULL()）;
returns:

+-------+-------------+

| year  | SUM(profit) |

+-------+-------------+

|  2000 |        4525 |

|  2001 |        3010 |

| Total |        7535 |

+-------+-------------+

另外，当GROUP BY多个列时，WITH ROLLUP影响更甚。此时每一列额外都会做一个整体汇总。

exp：

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product;
+------+---------+------------+-------------+
| year | country | product    | SUM(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 ROLL UP会输出每一年份/国家/产品的一个整体汇总，如下：

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+-------------+
| year | country | product    | SUM(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 |
+------+---------+------------+-------------+

注意一下置为NULL的列，例如当统计2000年USA的所有产品产生的利润，此时产品置为NULL，其余相似。具体解释官方有。

 

另外，当我们使用WITH ROLL UP时还需要考虑一些其他问题。

 

1、不能直接使用ORDER BY排序，两者是相互独立的，不过可以对查询出来的结果集（子查询/中间表）进行排序（注意排序的列名）。

mysql> SELECT * FROM (SELECT year, country, SUM(profit) FROM sales GROUP BY year WITH ROLLUP) 
    -> derived_t1 ORDER BY year;

2、LIMIT子句放在WITH ROLL UP之后，不过加上这个，会影响WITH ROLL UP的效果：

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP
    -> LIMIT 5;


+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
+------+---------+------------+-------------+

3、列置NULL的规则：

在查询结果发往客户端时，额外的汇总（列）置为NULL值。服务器着眼于被命名在GROUP BY子句中的那些列中最左边已经改变值的列，如year。如果结果集的显示列与GROUP BY的列名匹配的话（官方原文：For any column in the result set with a name that is a lexical match to any of those names, its value is set toNULL.），那么它的值会设为NULL。如果用列number来说明grouping列，那么服务器会通过number来辨识哪些列的值设为NULL。（这一段有些不好理解，先这么翻译）

 

4、由于额外汇总行在结果集的下面（查询比较晚），不能添加HAVING col IN NULL子句筛选（除了额外汇总行之外）某列值为NULL的行，NULL值可以通过mysql客户端函数验证。

筛选额外汇总行例子：

NULL rows are inserted very late in the query process, but they can be used very well as described in the post above. Also they can be used with the HAVING clause.

mysql> SELECT IFNULL(url, 'ALL_URLS') AS url, IFNULL(year, 'ALL_YEARS') AS year, IFNULL(country, 'ALL_COUNTRIES') AS country, SUM(visit) FROM rollup_1 GROUP BY url, year, country WITH ROLLUP HAVING country is null;

+----------------------+-----------+---------------+------------+

| url                  | year      | country       | SUM(visit) |

+----------------------+-----------+---------------+------------+

| http://www.yahoo.com | 2005      | ALL_COUNTRIES |      22000 |

| http://www.yahoo.com | 2006      | ALL_COUNTRIES |      26700 |

| http://www.yahoo.com | 2007      | ALL_COUNTRIES |      34200 |

| http://www.yahoo.com | ALL_YEARS | ALL_COUNTRIES |      82900 |

| ALL_URLS             | ALL_YEARS | ALL_COUNTRIES |      82900 |

+----------------------+-----------+---------------+------------+

5 rows in set, 4 warnings (0.00 sec)

There is one gotcha, if the column value itself has a NULL value, you may see undefined behavior.

 

5、允许不在GROUP BY子句的列出现在查询列中：

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

 

6、不过当使用了ONLY_FULL_GROUP_BY模式，正规军之后，上面的操作会视为违法，因为上面的country并不在GROUP BY子句。