数据库ROLLUP与CUBE | Patrick's Space

概览

ROLLUP 和 CUBE 都是用来在差查询结果中生成合计的项目。

这两个关键字需要和 GROUP BY 来一起使用，需要注意的是 GROUP BY 关键字后面可以跟多个列名，这多个列名之间的顺序和输出的顺序是相关的。

ROLLUP 和 CUBE 的操作都要依赖 GROUP BY。

GROUPING

GROUPING 用来区分是本来内容就是 null 还是是因为 ROLLUP 和 CUBE 而产生的 null 值，如果是后者那么 GROUPING(列名) 返回的结果就是 1，否则则是零。

ROLLUP 统计的时候会默认生成 null，这时候就需要自己填入统计的结果了。

ROLLUP

ROLLUP 产生的合计项目和 GROUP BY 中的多个列名的顺序有关。

ROLLUP 会根据 GROUP BY 的第一个列来进行分组并且输出汇总的内容。

例子：

select case when grouping(city) = 1 then 'ALL'
	else city 
	end as city,
	case when grouping(state) = 1 then 'ALL'
	else state
	end as state
from pubs.dbo.authors
group by city, state with rollup;

运行结果

Ann Arbor	MI
Ann Arbor	ALL
……省略若干行
Vacaville	CA
Vacaville	ALL
Walnut Creek	CA
Walnut Creek	ALL
ALL	ALL

在这里是对应的每个不同的州进行分组，然后对每个组进行汇总，最终对所有的州再进行一次汇总。

CUBE

CUBE 产生的合计项目和 GROUP BY 中的多个列名的顺序在某种程度上可以说是无关。

CUBE 产生的结果不仅会产生 ROLLUP 的结果，还会产生 ROLLUP 中没出现的另一种顺序的结果。

例子：

select case when grouping(city) = 1 then 'ALL'
	else city 
	end as city,
	case when grouping(state) = 1 then 'ALL'
	else state
	end as state
from pubs.dbo.authors
group by city, state with cube;

结果：

Berkeley	CA
Covelo	CA
Menlo Park	CA
Oakland	CA
Palo Alto	CA
San Francisco	CA
San Jose	CA
Vacaville	CA
Walnut Creek	CA
ALL	CA
Gary	IN
ALL	IN
Lawrence	KS
ALL	KS
Rockville	MD
ALL	MD
Ann Arbor	MI
ALL	MI
Corvallis	OR
ALL	OR
Nashville	TN
ALL	TN
Salt Lake City	UT
ALL	UT
ALL	ALL
Ann Arbor	ALL
Berkeley	ALL
Corvallis	ALL
Covelo	ALL
Gary	ALL
Lawrence	ALL
Menlo Park	ALL
Nashville	ALL
Oakland	ALL
Palo Alto	ALL
Rockville	ALL
Salt Lake City	ALL
San Francisco	ALL
San Jose	ALL
Vacaville	ALL
Walnut Creek	ALL

如果忽视输出数据的顺序，那么这个结果相当于两个 ROLLUP 的结果作 UNION

select case when grouping(city) = 1 then 'ALL'
	else city 
	end as city,
	case when grouping(state) = 1 then 'ALL'
	else state
	end as state
from pubs.dbo.authors
group by city, state with rollup
union
select case when grouping(city) = 1 then 'ALL'
	else city 
	end as city,
	case when grouping(state) = 1 then 'ALL'
	else state
	end as state
from pubs.dbo.authors
group by state, city with rollup;