SQLSERVER中union,cube,rollup,cumpute运算符使用说明

/*
--1 union 运算符是将两个或更多查询的结果组合为单个结果集
使用 union 组合查询的结果集有两个最基本的规则：
1。所有查询中的列数和列的顺序必须相同。
2。数据类型必须兼容
a.union的结果集列名与第一个select语句中的结果集中的列名相同，其他select语句的结果集列名被忽略
b.默认情况下，union 运算符是从结果集中删除重复行。如果使用all关键字，那么结果集将包含所有行并且不删除重复行
c.sql是从左到右对包含union 运算符的语句进行取值，使用括号可以改变求值顺序
--例如:
*/
select * from tablea
union all
(
select * from tableb
union all
select * from tablec
)
/*
这样就可以先对tableb和tablec合并，再合并tablea
d.如果要将合并后的结果集保存到一个新数据表中，那么into语句必须加入到第一条select中
e.只可以在最后一条select语句中使用 order by 和 compute 子句，这样影响到最终合并结果的排序和计数汇总
f.group by 和 having 子句可以在单独一个select查询中使用，它们不影响最终结果
*/
--2 cube 汇总数据
/*
cube 运算符生成的结果集是多维数据集。多维数据集是事实数据的扩展，事实数据即记录个别事件的数据。
扩展建立在用户打算分析的列上。这些列被称为维。多维数据集是一个结果集，其中包含了各维度的所有可能组合的交叉表格。
cube 运算符在 select 语句的 group by 子句中指定。该语句的选择列表应包含维度列和聚合函数表达式。
group by 应指定维度列和关键字 with cube。结果集将包含维度列中各值的所有可能组合，以及与这些维度值组合相匹配的基础行中的聚合值。
*/
--下列查询返回的结果集中，将包含 item 和 color 的所有可能组合的 quantity 小计：
-->title:生成測試數據
-->author:wufeng4552
-->date :2009-09-10 14:36:20
if not object_id('tempdb..#t') is null
drop table #t
go
create table #t([item] nvarchar(5),[color] nvarchar(4),[quantity] int)
insert #t
select n'table',n'blue',124 union all
select n'table',n'red',223 union all
select n'chair',n'blue',101 union all
select n'chair',n'red',210
go
select [item],
[color],
sum([quantity])[quantity]
from #t group by [item],[color] with cube
/*
item color quantity
----- ----- -----------
chair blue 101
chair red 210
chair null 311
table blue 124
table red 223
table null 347
null null 658
null blue 225
null red 433
*/
/*cube 操作所生成的空值带来一个问题：如何区分 cube 操作所生成的 null 值和从实际数据中返回的 null 值？
这个问题可用 grouping 函数解决。
如果列中的值来自事实数据，则 grouping 函数返回 0；如果列中的值是 cube 操作所生成的 null，则返回 1。
在 cube 操作中，所生成的 null 代表全体值。可将 select 语句写成使用 grouping 函数将所生成的 null 替换为字符串 all。
因为事实数据中的 null 表明数据值未知，所以 select 语句还可译码为返回字符串 unknown 替代来自事实数据的 null。
例如：
*/
-->title:生成測試數據
-->author:wufeng4552
-->date :2009-09-10 14:36:20
if not object_id('tempdb..#t') is null
drop table #t
go
create table #t([item] nvarchar(5),[color] nvarchar(4),[quantity] int)
insert #t
select n'table',n'blue',124 union all
select n'table',n'red',223 union all
select n'chair',n'blue',101 union all
select n'chair',n'red',210
go
select [item]=case when grouping([item])=1 then 'all' else isnull(item, 'unknown')end,
[color]=case when grouping([color])=1 then 'all' else isnull([color],'unknown')end,
sum([quantity])[quantity]
from #t group by [item],[color] with cube
/*
item color quantity
----- ----- -----------
chair blue 101
chair red 210
chair all 311
table blue 124
table red 223
table all 347
all all 658
all blue 225
all red 433
(9 個資料列受到影響)
*/
/*
包含带有许多维度的 cube 的 select 语句可能生成很大的结果集，因为这些语句会为所有维度中值的所有组合生成行。
这些大结果集包含的数据可能过多而不易于阅读和理解。这个问题有一种解决办法是将 select 语句放在视图中：
*/
create view view_cube
as
select [item]=case when grouping([item])=1 then 'all' else isnull(item, 'unknown')end,
[color]=case when grouping([color])=1 then 'all' else isnull([color],'unknown')end,
sum([quantity])[quantity]
from tb group by [item],[color] with cube --視圖中不能用臨時表,故改之
--然后即可用该视图来只查询您感兴趣的维度值：
select *
from invcube
where item = 'chair' and color = 'all'
/*
item color qtysum
-------------------- -------------------- ---------
chair all 311.00
*/
--3 rollup 汇总数据
/*
用 rollup 汇总数据在生成包含小计和合计的报表时，rollup 运算符很有用。
rollup 运算符生成的结果集类似于 cube 运算符所生成的结果集。
cube 和 rollup 之间的区别在于： cube 生成的结果集显示了所选列中值的所有组合的聚合。
rollup 生成的结果集显示了所选列中值的某一层次结构的聚合。 例如，简单表 #t
中包含：item color quantity
*/
select [item]=case when grouping([item])=1 then 'all' else isnull(item, 'unknown')end,
[color]=case when grouping([color])=1 then 'all' else isnull([color],'unknown')end,
sum([quantity])[quantity]
from #t group by [item],[color] with rollup
/*
item color quantity
----- ----- -----------
chair blue 101
chair red 210
chair all 311
table blue 124
table red 223
table all 347
all all 658
(7 個資料列受到影響)
*/
/*
如果查询中的 rollup 关键字更改为 cube，那么 cube 结果集与上述结果相同，只是在结果集的末尾还会返回下列两行：all blue 225.00
all red 433.00
cube 操作为 item 和 color 中值的可能组合生成行。
例如，cube 不仅报告与 item 值 chair 相组合的 color 值的所有可能组合（red、blue 和 red + blue），
而且报告与 color 值 red 相组合的 item 值的所有可能组合（chair、table 和 chair + table）。
对于 group by 子句中右边的列中的每个值，rollup 操作并不报告左边一列（或左边各列）中值的所有可能组合。例如，
rollup 并不对每个 color 值报告 item 值的所有可能组合。
rollup 操作的结果集具有类似于 compute by 所返回结果集的功能；然而，rollup 具有下列优点： rollup 返回单个结果集；compute by 返回多个结果集，而多个结果集会增加应用程序代码的复杂性。
rollup 可以在服务器游标中使用；compute by 不可以。
有时，查询优化器为 rollup 生成的执行计划比为 compute by 生成的更为高效。
*/