日期:2014-05-18 浏览次数:20745 次
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(Color)=0 THEN Groups WHEN GROUPING(Groups)=1 THEN '总计' ELSE '' END, Item=CASE WHEN GROUPING(Color)=0 THEN Item WHEN GROUPING(Item)=1 AND GROUPING(Groups)=0 THEN Groups+' 合计' ELSE '' END, Color=CASE WHEN GROUPING(Color)=0 THEN Color WHEN GROUPING(Color)=1 AND GROUPING(Item)=0 THEN Item+' 小计' ELSE '' END, Quantity=SUM(Quantity) FROM @t GROUP BY Groups,Item,Color WITH ROLLUP ORDER BY GROUPING(Groups), CASE WHEN GROUPING(Groups)=1 THEN '' ELSE Groups END DESC, GROUPING(Item), CASE WHEN GROUPING(Item)=1 THEN '' ELSE Item END DESC, GROUPING(Color), CASE WHEN GROUPING(Color)=1 THEN '' ELSE Color END DESC, Quantity DESC /*--结果 Groups Item Color Quantity --------- -------------- ----------------------- ----------- bb Table Red -23 Table 小计 -23 bb Cup Green -23 Cup 小计 -23 bb 合计 -46 aa Table Blue 124 Table 小计 124 aa Chair Red -90 aa Chair Blue 101 Chair 小计 11 aa 合计 135 总计 89 --*/