SQL SERVER中GROUPING SETS,CUBE,ROLLUP
by
admin on 2014-03-25 12:16:37 in
,
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
-
)
-
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 生成的结果集显示了所选列中值的所有组合的聚合。
聚合函数GROUPING: 当用当结果集中的行是由GROUPING SETS,CUBE,或ROLLUP生成的,则值为1否则为0。
如果没有用CASE WHEN判断GROUPING,则上面所有的allnames,allcategories会被NULL替代。
评论