SQL SERVER中GROUPING SETS，CUBE，ROLLUP
前言
全文我们将使用一个CTE语句作为基础数据:
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
使用 GROUPING SETS 的 GROUP BY 子句可以生成一个等效于由多个简单 GROUP BY 子句的 UNION ALL 生成的结果集。
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 |
ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合，如“DePaul 70，LeeWhoeeUniversity 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
)
selec