SQL SERVER中GROUPING SETS，CUBE，ROLLUP
前言
with test
as
(
select N‘LeeWhoeeUnisersity‘ as name,N‘数据库‘ as category, 30 as totalcount
union all
select N‘LeeWhoeeUnisersity‘,‘.NET‘,20
union all
select N‘DePaul‘,N‘.NET‘,40
union all
select N‘DePaul‘,N‘WEB设计‘,30
)
select * from test
结果集是：
name category totalcount
LeeWhoeeUnisersity 数据库 30
LeeWhoeeUnisersity .NET 20
DePaul .NET 40
DePaul WEB设计 30
GROUPING SETS
SELECT customer, year, SUM(sales) FROM T GROUP BY GROUPING SETS ((customer), (year)) 和 SELECT customer, NULL as year, SUM(sales) FROM T GROUP BY customer UNION ALL SELECT NULL as customer, year, SUM(sales) FROM T GROUP BY year 是等效的。
看实例：
with test
as
(
select N‘LeeWhoeeUnisersity‘ as name,N‘数据库‘ as category, 30 as totalcount
union all
select N‘LeeWhoeeUnisersity‘,‘.NET‘,20
union all
select N‘DePaul‘,N‘.NET‘,40
union all
select N‘DePaul‘,N‘WEB设计‘,30
)
select name,category,sum(totalcount) as [sum] from test
group by grouping sets ((name),(category))
结果：
| name | category | sum |
|---|---|---|
| .NET | 60 | |
| WEB设计 | 30 | |
| 数据库 | 30 | |
| DePaul | 70 | |
| LeeWhoeeUnisersity | 50 |
ROLLUP
GROUP BY ROLLUP (C1, C2, …, Cn-1, Cn)或者GROUP BY C1, C2, …, Cn-1, Cn WITH ROLLUP
和
GROUP BY GROUPING SETS ( (C1, C2, …, Cn-1, Cn)
,(C1, C2, ..., Cn-1)
...
,(C1, C2)
,(C1)
,() )
是等效的。注意WITH ROLLUP是旧版本的写法，GROUP BY ROLLUP 只能运行于兼容性100以上的版本。
with test
as
(
select N‘LeeWhoeeUnisersity‘ as name,N‘数据库‘ as category, 30 as totalcount
union all
select N‘LeeWhoeeUnisersity‘,‘.NET‘,20
union all
select N‘DePaul‘,N‘.NET‘,40
union all
select N‘DePaul‘,N‘WEB设计‘,30
)
select name,category,sum(totalcount) as [sum] from test
group by rollup (name,category)相当于with test
as
(
select N‘LeeWhoeeUnisersity‘ as name,N‘数据库‘ as category, 30 as totalcount
union all
select N‘LeeWhoeeUnisersity‘,‘.NET‘,20
union all
select N‘DePaul‘,N‘.NET‘,40
union all
select N‘DePaul‘,N‘WEB设计‘,30
)
select name,category,sum(totalcount) as [sum] from test
group by grouping sets ((name,category),(name),())
结果：
| name | category | sum |
|---|---|---|
| DePaul | .NET | 40 |
| DePaul | WEB设计 | 30 |
| DePaul | 70 | |
| LeeWhoeeUnisersity | .NET | 20 |
| LeeWhoeeUnisersity | 数据库 | 30 |
| LeeWhoeeUnisersity | 50 | |
| 120 |
CUBE
GROUP BY CUBE (C1, C2, C3)等效于GROUP BY GROUPING SETS ( (C1, C2, C3) ,(C1, C2) ,(C1, C3) ,(C2, C3) ,(C1) ,(C2) ,(C3) ,() )
进行CUBE测试：
with test
as
(
select N‘LeeWhoeeUnisersity‘ as name,N‘数据库‘ as category, 30 as totalcount
union all
select N‘LeeWhoeeUnisersity‘,‘.NET‘,20
union all
select N‘DePaul‘,N‘.NET‘,40
union all
select N‘DePaul‘,N‘WEB设计‘,30
)
select case when grouping(name)=1 then ‘allnames‘ else name end as name
,case when grouping(category)=1 then ‘allcategories‘ else category end as category
,sum(totalcount) as sum
from test
group by cube(name,category)
相当于
with test
as
(
select N‘LeeWhoeeUnisersity‘ as name,N‘数据库‘ as category, 30 as totalcount
union all
select N‘LeeWhoeeUnisersity‘,‘.NET‘,20
union all
select N‘DePaul‘,N‘.NET‘,40
union all
select N‘DePaul‘,N‘WEB设计‘,30
)
select case when grouping(name)=1 then ‘allnames‘ else name end as name
,case when grouping(category)=1 then ‘allcategories‘ else category end as category
,sum(totalcount) as sum
from test
group by grouping sets((name,category),(name),(category),())
结果：
name category sum
LeeWhoeeUnisersity .NET 20
DePaul .NET 40
allnames .NET 60
DePaul WEB设计 30
allnames WEB设计 30
LeeWhoeeUnisersity 数据库 30
allnames 数据库 30
allnames allcategories 120
LeeWhoeeUnisersity allcategories 50
DePaul allcategories 70
CUBE 生成的结果集显示了所选列中值的所有组合的聚合。
如果没有用CASE WHEN判断GROUPING，则上面所有的allnames，allcategories会被NULL替代。