grouping、with rollup的用法。

DECLARE @t TABLE(Groups char(2),Item varchar(10),Color varchar(10),Quantity int)
INSERT @t SELECT 'aa','Table','Blue', 124
UNION ALL SELECT 'bb','Table','Red',  -23
UNION ALL SELECT 'bb','Cup'  ,'Green',-23
UNION ALL SELECT 'aa','Chair','Blue', 101
UNION ALL SELECT 'aa','Chair','Red',  -90

SELECT Groups=CASE  
        WHEN GROUPING(Groups)=1 THEN '总计'
  WHEN GROUPING(ITEM)=1 AND GROUPING(color)=1 THEN Groups+'合计'
        ELSE Groups END,
    Item=CASE 
        WHEN GROUPING(color)=1 AND GROUPING(Item)=0 THEN item+' 合计'
  WHEN GROUPING(color)=1 AND GROUPING(Item)=1 THEN ''
        ELSE Item END,
    Color=CASE 
        WHEN GROUPING(Color)=0 THEN Color
        ELSE '' END,
    Quantity=SUM(Quantity)
FROM @t
GROUP BY Groups,Item,Color WITH ROLLUP
/*
Groups Item            Color      Quantity    
------ --------------- ---------- ----------- 
aa     Chair           Blue       101
aa     Chair           Red        -90
aa     Chair 合计                   11
aa     Table           Blue       124
aa     Table 合计                   124
aa合计                              135
bb     Cup             Green      -23
bb     Cup 合计                     -23
bb     Table           Red        -23
bb     Table 合计                   -23
bb合计                              -46
总计                                89

（所影响的行数为 12 行）
*/