TSql Grouping Sets

OBJECT_ID(‘dbo.Inventory‘) is not null drop table dbo.Inventory go create table dbo.Inventory ( Store varchar(2), Item varchar(20), Color varchar(10), Quantity int ) insert into dbo.Inventory values(‘NY‘,‘Table‘,‘Blue‘,124) insert into dbo.Inventory values(‘NJ‘,‘Table‘,‘Blue‘,100) insert into dbo.Inventory values(‘NY‘,‘Table‘,‘Red‘,29) insert into dbo.Inventory values(‘NJ‘,‘Table‘,‘Red‘,56) insert into dbo.Inventory values(‘PA‘,‘Table‘,‘Red‘,138) insert into dbo.Inventory values(‘NY‘,‘Table‘,‘Green‘,229) insert into dbo.Inventory values(‘PA‘,‘Table‘,‘Green‘,304) insert into dbo.Inventory values(‘NY‘,‘Chair‘,‘Blue‘,101) insert into dbo.Inventory values(‘NJ‘,‘Chair‘,‘Blue‘,22) insert into dbo.Inventory values(‘NY‘,‘Chair‘,‘Red‘,21) insert into dbo.Inventory values(‘NJ‘,‘Chair‘,‘Red‘,10) insert into dbo.Inventory values(‘PA‘,‘Chair‘,‘Red‘,136) insert into dbo.Inventory values(‘NJ‘,‘Sofa‘,‘Green‘,2)

2， grouping set 是()

select null as Item, null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory

--等价于
select  null as Item, null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory
group by grouping sets(())

2， grouping set 是(a,b）

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from Inventory 
group by  Item,Color 
order by Item,Color

--等价于
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from Inventory 
group by grouping sets( (Item,Color)) 
order by Item,Color

3，grouping set是(a)

select Item,null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by  Item 
order by Item

--等价于
select Item,null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets( (Item))  --(Item)
order by Item

4,grouping set是(b)

select null as Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by  Color 
order by Color

--等价于
select null as Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets( (Color)) --(Color)
order by Color

 

5,group by rollup(a,b)的grouping set是(),(a),(a,b)

--rollup(a,b)的grouping sets是(),(a),(a,b)
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by ROLLUP(Item,Color) 
order by Item,Color

--等价于
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets((),(Item),(Item,Color))
order by Item,Color

6，group by cube(a,b)的grouping set是(),(a),(b),(a,b)

--rollup(a,b)的grouping sets是(),(a),(a,b)
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by ROLLUP(Item,Color) 
order by Item,Color

--等价于
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets((),(Item),(Item,Color))
order by Item,Color

7，对rollup(a,b)或cube(a,b)也可以使用相同的grouping set对应的group by和union来实现，但是代码量比价多，

--rollup(a,b)的grouping sets是(),(a),(a,b)
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by ROLLUP(Item,Color) 
order by Item,Color


--等价于

select null as Item, null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory

union ALL

select Item,null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by  Item 

union ALL

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by  Item,Color 

order by Item,Color

 

--rollup(a,b)的grouping sets是(),(a),(a,b)
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by cube(Item,Color) 
order by Item,Color


--等价于

select null as Item, null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory

union ALL

select Item,null as Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by  Item 

union ALL

select null as Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by  Color 

union all

select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by  Item,Color 

order by Item,Color

 

8，group by grouping sets(rollup(a,b),b) 等价于 group by cube(a,b)，因为rollup(a,b)的grouping set是(),(a),(a,b),在加上(b)，等价于cube(a,b).

--cube(a,b)的组合是(),(a),(b),(a,b)
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from Inventory 
group by CUBE( Item,Color) 
order by Item,Color

--等价于
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets((),(Item),(Color),(Item,Color))
order by Item,Color

--等价于
select Item,Color,SUM(Quantity) as TotalQuantity,COUNT(Store) as Stores 
from dbo.Inventory 
group by grouping sets(rollup(Item,Color),(Color))
order by Item,Color

 

总结：

1， cube和rollup定义了grouping set的简单方式，rollup(a,b)定义的grouping set是(),(a),(a,b), cube(a,b)定义的grouping set是(),(a),(b),(a,b)。

2，grouping函数和grouping_id函数 用来判断字段是否属于表中的数据行。

 

Grouping(字段名) 用来区分当前行是不是小计产生的行，  Grouping(字段名)=1 说明是统计行，Grouping(字段名)=0 说明是表中行

Group_id(a,b,c)

  

参考文档

https://msdn.microsoft.com/zh-cn/library/bb510624(v=sql.110).aspx

https://msdn.microsoft.com/zh-cn/library/ms178544(v=sql.110).aspx https://msdn.microsoft.com/zh-cn/library/ms177673(v=sql.110).aspx

 

TSql Grouping Sets

标签：