90.UNION ALL 实现的分级汇总案例

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
FROM(
	--明细
	SELECT Item,Color,Quantity=SUM(Quantity)
		,s1=0,s2=Item,s3=0
	FROM @t
	GROUP BY Item,Color
	UNION ALL
	--各Item合计
	SELECT '',Item+' 合计',Quantity=SUM(Quantity)
		,s1=0,s2=Item,s3=1
	FROM @t
	GROUP BY Item
	UNION ALL
	--总计
	SELECT '总计','',Quantity=SUM(Quantity)
		,s1=1,s2='',s3=1
	FROM @t
)a ORDER BY s1,s2,s3
/*--结果
Item       Color           Quantity
-------------- ---------------------- ----------- 
Chair      Blue            101
Chair      Red             -90
          Chair 合计       11
Table      Red             -23
Table      Blue            124
          Table 合计       101
总计                      112
--*/