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

学到新知识了, 去找了下,

如果 GROUP BY A,B,C WITH ROLLUP，那么结果是 GROUP BY A,B,C 和 GROUP BY A,B 和
GROUP BY A 和 合并全表的一次查询。

将 group by 前面字段中使用的函数在 group by a,b,c group by a,b group by a 在此之外还有个不加任何 group by 的合并值.

这里有篇文章, 介绍的比较详细.
http://sqlandme.com/2011/07/07/sql-server-tsql-group-by-with-rollup/

此语句在做数据统计的时候非常有效