SQL系列ROLLUP关键字

为什么要引入ROLLUP？
可方便的生成"合计"、"小计"、"总计" 等混合统计的报表
如下数据集A经过ROLLUP运算可生成数据集B

 

1.ROLLUP简单应用
SELECT Groups,Item,Color FROM @t GROUP BY Groups,Item,Color  WITH ROLLUP
查询结果如下：

如果用ROLLUP的另一个关键字CUBE产生的结果如下:

SELECT Groups,Item,Color FROM @t GROUP BY Groups,Item,Color  WITH CUBE

 

引出一个概念:
维：查询所指定的列称为维，列的个数称为维度
以上两个SQL语句涉及三个维度，ROLLUP和CUBE都可以用于计算，不同点在于CUBE可以包含所有纬度的组合，而
ROLLUP只涉及指定列的维度计算。

 

2.ROLLUP运算关键字GROUPING
GROUPING：判断是否为计算中的附加列，如果是则输出1，否则输出0

 

SELECT Groups= CASE  WHEN GROUPING(GROUPS)=1 THEN 1 ELSE 0 END ,Item,Color FROM @t GROUP BY Groups,Item,Color WITH ROLLUP

结果:

引入ROLLUP可以方便的对查询结果排序或自定义附加列的格式

 

排序应用：

SELECT Groups,Item,Color FROM @t GROUP BY Groups,Item,Color WITH ROLLUP ORDER BY GROUPING(Groups),Groups DESC

结果:

补充：关于汉字、字母、null、空值、数字的排序问题

 

 SELECT * FROM @t ORDER BY groups DESC
结果：

至于自定义附加列的样式在第三步讲解

 

3.ROLLUP综合运用

 

SELECT Groups=CASE    
  WHEN GROUPING(Color)=0 THEN Groups   
  WHEN GROUPING(Groups)=1 THEN '总计'  
  ELSE '' END,   
 Item=CASE    
  WHEN GROUPING(Color)=0 THEN Item   
  WHEN GROUPING(Item)=1 THEN Groups+' 合计'  
  ELSE '' END,   
 Color=CASE    
  WHEN GROUPING(Color)=0 THEN Color   
  WHEN GROUPING(Color)=1 THEN Item+' 小计'  
  ELSE '' END,   
 Quantity=SUM(Quantity)   
FROM @t   
GROUP BY Groups,Item,Color WITH ROLLUP  
ORDER BY GROUPING(Groups),   
CASE WHEN GROUPING(Groups)=0 THEN Groups END  DESC,   
GROUPING(Item),   
CASE WHEN GROUPING(Item)=0 THEN Item END  DESC,   
GROUPING(Color),   
CASE WHEN GROUPING(Color)=0 THEN Color END  DESC

 

结果:

 

 

--------------------------------------------------------------------------------------------------------------------------------------

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.

 

可以看到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