统计报表(用ROLLUP 汇总数据)

原贴：http://community.csdn.net/Expert/topic/4313/4313978.xml?temp=.691601

表Inventory
Item                 Color                Quantity                  
-------------------- -------------------- --------------------------
Table                Blue                 124                       
Table                Red                  223                       
Chair                Blue                 101                       
Chair                Red                  210 

要得到下面结果:

Item                 Color                QtySum                    
-------------------- -------------------- --------------------------
Chair                Blue                 101.00                    
Chair                Red                  210.00                    
Chair小计                                 311.00                    
Table                Blue                 124.00                    
Table                Red                  223.00                    
Table小计                                 347.00                    
总计                                      658.00                    

---该问题是一个典型的使用Rollup生成结合的例子,联机帮助也有相关介绍！

-测试环境
declare @Inventory Table (Item varchar(20),Color varchar(20),Quantity money)
insert into @Inventory select 'Table','Blue',124
insert into @Inventory select 'Table','Red' ,223
insert into @Inventory select 'Chair','Blue',101
insert into @Inventory select 'Chair','Red' ,210

--查询
SELECT CASE WHEN (GROUPING(Item) = 1) THEN '总计'
    WHEN (GROUPING(Color) = 1) THEN Item+'小计'
            ELSE ISNULL(Item, 'UNKNOWN')
       END AS Item,
       CASE WHEN (GROUPING(Color) = 1) THEN ''
            ELSE ISNULL(Color, 'UNKNOWN')
       END AS Color,
       SUM(Quantity) AS QtySum
FROM @Inventory
GROUP BY Item, Color WITH ROLLUP

--结果
Item                     Color                QtySum               
------------------------ -------------------- ---------------------
Chair                    Blue                 101.0000
Chair                    Red                  210.0000
Chair小计                                       311.0000
Table                    Blue                 124.0000
Table                    Red                  223.0000
Table小计                                       347.0000
总计                                            658.0000

（所影响的行数为 7 行）