SQL SERVER中GROUPING SETS，CUBE，ROLLUP

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替代。