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 生成的更为高效。 
*/