MySQL 的 GROUP BY ... WITH ROLLUP

Question

在书上看到了 WITH ROLLUP 上滚查询的概念，我测试了一下，我的理解是如果 GROUP BY A,B,C WITH ROLLUP，那么结果是 GROUP BY A,B,C 和 GROUP BY A,B 和 GROUP BY A 和 合并全表的一次查询。
测试了一下：

-- 创建表 sales 并初始化数据
CREATE TABLE sales(
    YEAR INT NOT NULL,
    country VARCHAR(20) NOT NULL,
    product VARCHAR(20) NOT NULL,
    profit INT
);

INSERT INTO sales VALUES(2004,'china','tnt1',2001);
INSERT INTO sales VALUES(2004,'china','tnt2',2002);
INSERT INTO sales VALUES(2004,'china','tnt3',2003);
INSERT INTO sales VALUES(2005,'china','tnt1',2004);
INSERT INTO sales VALUES(2005,'china','tnt2',2005);
INSERT INTO sales VALUES(2005,'china','tnt3',2006);
INSERT INTO sales VALUES(2005,'china','tnt1',2007);
INSERT INTO sales VALUES(2005,'china','tnt2',2008);
INSERT INTO sales VALUES(2005,'china','tnt3',2009);
INSERT INTO sales VALUES(2006,'china','tnt1',2010);
INSERT INTO sales VALUES(2006,'china','tnt2',2011);
INSERT INTO sales VALUES(2006,'china','tnt3',2012); 

-- 使用 WITH ROLLUP
SELECT YEAR,country,product,SUM(profit) FROM sales GROUP BY YEAR,country,product WITH ROLLUP;

-- 等同于
SELECT YEAR,country,product,SUM(profit) FROM sales GROUP BY YEAR,country,product
UNION ALL
SELECT YEAR,country,NULL AS product,SUM(profit) FROM sales GROUP BY YEAR,country
UNION ALL
SELECT YEAR,NULL AS country,NULL AS product,SUM(profit) FROM sales GROUP BY YEAR
UNION ALL
SELECT NULL AS YEAR,NULL AS country,NULL AS product,SUM(profit) FROM sales; 

这两种查询的出来的结果是一致的（最后一次查询是合并全表的，但是我不知道怎么合并全表，这里是 SUM(profit) 达到了同样的效果）。

不知道理解对不对。

Answer 1

I learned something new, I went to look for it,

If GROUP BY A,B,C WITH ROLLUP, then the result is GROUP BY A,B,C and GROUP BY A,B and
GROUP BY A and merge the entire table in one query.

The function used in the previous field of group by is group by a,b,c group by a,b group by a. In addition, there is a merged value without any group by.

Here is an article with a more detailed introduction.
http://sqlandme.com/2011/07/07/sql-server-tsql-group-by-with-rollup/

This statement is very effective when doing data statistics