mysql 多表with rollup_MySQL 的 GROUP BY ... WITH ROLLUP

在书上看到了 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) 达到了同样的效果)。

不知道理解对不对。