94.ROLLUP 实际的分级汇总案例（带排序及会中列标题处理）

刺骨的言语ヽ痛彻心扉 2024-02-19 18:17 78阅读 0赞

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
    --*/