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