邹老大写的，不太明白啊！
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)
,a1=0,a2=Item,a3=0
FROM @t
GROUP BY Item,Color
UNION ALL
--各Item合计
SELECT ' ',Item+ ' 合计 ',Quantity=SUM(Quantity)
,a1=0,a2=Item,a3=1
FROM @t
GROUP BY Item
UNION ALL
--总计
SELECT '总计 ', ' ',Quantity=SUM(Quantity)
,a1=1,a2= ' ',a3=1
FROM @t
)a
ORDER BY a1,a2,a3
------------------------
/*--结果
Item Color Quantity
-------------- ---------------------- -----------
Chair Blue 101
Chair Red -90
Chair 合计 11
Table Red -23
Table Blue 124
Table 合计 101
总计 112
--*/
------------------------
我的想法是：
/*--结果
Item Color Quantity s1 s2 s3
-------------- ---------------------- -----------
Chair Blue 101 ...... ...... ......
Chair Red -90
Chair 合计 11
Table Red -23
Table Blue 124
Table 合计 101
总计 112
--*/
----------------------------------------
为什么S1，S2，S3没有出现呢，这是一种什么情况？
[解决办法]
SELECT Item,Color,Quantity,a1,a2,a3
FROM(
--明细
SELECT Item,Color,Quantity=SUM(Quantity)
,a1=0,a2=Item,a3=0
FROM @t
GROUP BY Item,Color
UNION ALL
--各Item合计
SELECT ' ',Item+ ' 合计 ',Quantity=SUM(Quantity)
,a1=0,a2=Item,a3=1
FROM @t
GROUP BY Item
UNION ALL
--总计
SELECT '总计 ', ' ',Quantity=SUM(Quantity)
,a1=1,a2= ' ',a3=1
FROM @t
)a
ORDER BY a1,a2,a3
