6.2.1 rollup实现的分级汇总示例.sql

sqlserver 数据库编程的绝好脚本

DECLARE @t TABLE(Item varchar(10),Color varchar(10),Quantity int)
INSERT @t SELECT 'Table','Blue',124
UNION ALL SELECT 'Table','Red', -23
UNION ALL SELECT 'Chair','Blue',101
UNION ALL SELECT 'Chair','Red', -90

--统计
SELECT Item,Color,Quantity=SUM(Quantity)
FROM @t
GROUP BY Item,Color WITH ROLLUP
/*--结果
Item       Color      Quantity    
---------- ---------- ----------- 
Chair      Blue       101
Chair      Red        -90
Chair      NULL     11
Table      Blue       124
Table      Red        -23
Table      NULL      101
NULL     NULL      112
--*/