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

select * from tablea
union all
(
select * from tableb
union all
select * from tablec
)

--2 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


-->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


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'

--3 ROLLUP 汇总数据

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