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/

此語句在做資料統計的時候非常有效