mssql sqlserver 创建分组(group sets)


摘要:
下文主要讲述grouping sets 自定义分组集的用法
以及grouping sets 分组集拆解为group by 的方法
以及grouping sets 和rollup cube 之间的等同关系

分组集常用语法:
group by 分组列1,分组列2…
group by rollup(分组列1,分组列2)
group by cube (分组列1,分组列2)
group by grouping sets((),(a),(a,b), rollup(a,b),cube(a,b))


grouping sets 可以实现group by 、group by rollup、 group by cube 三种不同的语法结构。
———————————————————————
语法说明
1: group by 分组列1,分组列2 等同于 group by grouping sets (分组列1,分组列2)
等同于 group by 分组列1 * group by 分组列2

2: group by rollup(分组列1,分组列2) 等同于 group by grouping sets ((),(分组列1),(分组列1,分组列2))

3: group by cube(分组列1,分组列2) 等同于 group by grouping sets ((),(分组列1),(分组列2),(分组列1,分组列2))

4: group by grouping set ((),(分组列1),(分组列1,分组列2)) 等同于 group by 分组列1 union all group by 分组列1,分组列2

———————————————————————
例:

 /*创建基础数据*/
 create table test(
 name varchar(30), sort varchar(30),
 qty int,priceAll int
 )
 go
 /*生成原始数据*/
 insert into test (name,sort,qty,priceAll)values('洗衣粉','a',100,1200)
 insert into test (name,sort,qty,priceAll)values('洗发水','a',90,800)
 insert into test (name,sort,qty,priceAll)values('肥皂','b',60,100)
 insert into test (name,sort,qty,priceAll)values('牙刷','c',30,50)
 insert into test (name,sort,qty,priceAll)values('洗发水','a',60,160)
 insert into test (name,sort,qty,priceAll)values('肥皂','b',70,120)
 insert into test (name,sort,qty,priceAll)values('牙刷','c',10,20)
 go
 
 /*数据汇总-全集*/
 select null as name,null as sort,sum(qty),sum(priceAll) from test 
 ---等同于 
 select null as name,null as sort,sum(qty),sum(priceAll) from test 
 group by grouping sets(())
 go
 -----------------------------------------------------------------------
 /*单列分组*/
 select  name,null as sort,sum(qty),sum(priceAll) from test 
 group by grouping sets((name))
 ----等同于
  select  name,null as sort,sum(qty),sum(priceAll) from test 
 group by name
 go
 --------------------------------------------------------------------------
  /*多列分组*/
 select  name, sort,sum(qty),sum(priceAll) from test 
 group by grouping sets((name,sort))
 ----等同于
  select  name,sort,sum(qty),sum(priceAll) from test 
 group by name,sort
 go
 --------------------------------------------------------------------------
  /*rollup分组*/
 select  name, sort,sum(qty),sum(priceAll) from test 
 group by grouping sets((),(name),(name,sort))
 ----等同于
  select  name,sort,sum(qty),sum(priceAll) from test 
 group by rollup(name,sort)
 go
 ----------------------------------------------------------------------------
   /*cube分组*/
 select  name, sort,sum(qty),sum(priceAll) from test 
 group by grouping sets((),(name),(sort),(name,sort))
 ----等同于
  select  name,sort,sum(qty),sum(priceAll) from test 
 group by cube(name,sort)
 go
 
 go
 truncate table test 
 drop table test