MSSql2005: Cube 和 RollUp 的区别
The differences between CUBE and ROLLUP are:
-
CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.
-
ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.
看完之后还是不知所云，所以按照书上的例子，测试了一下结果(先附上数据源和结果比较图，相关sql语句附在文后)：
可以看到Cube 比 RollUp多了三行.一比较发现，多的三行都是以Color列为纬度统计的。再结合书上说的那点区别，说RollUp是按层统计的。
猜想区别就是Cube对每个纬度都统计了，而RollUp对Group by 后跟的第一个纬度按照值不同顺序统计完后，终止在Null,NUll,1117这一行，不再继续。
我又加了一列Dept，调整之后再次比较结果，证实了我的猜想，RollUp以Item作为基本纬度，统计完所有情况后，就终止在Null,Null,NUll,1117这一行了。
下面附上sql语句：
CREATE TABLE Inventory (
Item varchar(53) NOT NULL,
Color varchar(53) NULL,
Department varchar(53) Null,
Quantity int NULL
);
INSERT Inventory VALUES ('Table', 'Red','IT', 223);
INSERT Inventory VALUES ('Table', 'White','HR', 54);
INSERT Inventory VALUES ('Table', 'Blue','ADM', 124);
INSERT Inventory VALUES ('Chair', 'Red', 'IT',210);
INSERT Inventory VALUES ('Chair', 'Blue','HR', 101);
INSERT Inventory VALUES ('Chair', 'White','ADM', 23);
INSERT Inventory VALUES ('Stand', 'Red','IT', 213);
INSERT Inventory VALUES ('Stand', 'Blue','HR', 141);
INSERT Inventory VALUES ('Stand', 'White','ADM', 28);
SELECT * FROM Inventory
SELECT Item, Color, Department,SUM(Quantity) AS Quantity
FROM Inventory
GROUP BY Item, Color,Department WITH CUBE;
SELECT Item, Color, Department,SUM(Quantity) AS Quantity
FROM Inventory
GROUP BY Item, Color,Department WITH Rollup
