96.分级汇总

素颜马尾好姑娘i 2024-02-19 18:58 51阅读 0赞

--示例
    
    --测试数据
    create table tb([DB-ID] varchar(10),ENTITY varchar(10),DATE varchar(10),[CUST-NO] int,AMOUNT decimal(10,2),TAX decimal(10,2))
    insert tb select 'RCHQ','001','2004-11-10',200000,100.00,17.00
    union all select 'RCHQ','001','2004-11-10',200000,200.00,34.00
    union all select 'RCHQ','001','2004-11-12',200000,150.00,25.50
    union all select 'RCHQ','002','2004-11-10',200000,100.00,17.00
    union all select 'RCHQ','002','2004-11-10',200000,200.00,34.00
    union all select 'RCHQ','002','2004-11-12',200000,150.00,25.50
    go
    
    --查询
    select [DB-ID],ENTITY,DATE,[CUST-NO],AMOUNT,TAX
    from(
    	select [DB-ID]=case 
    			when grouping([DB-ID])=1 then '合计'
    			else [DB-ID] end
    		,ENTITY=case 
    			when grouping([DB-ID])=1 then ''
    			when grouping(ENTITY)=1 then '小计'
    			else ENTITY end
    		,DATE=case 
    			when grouping([DB-ID])=1 then ''
    			when grouping(ENTITY)=1 then ''
    			when grouping(DATE)=1 then '小计'
    			else DATE end
    		,[CUST-NO]=case 
    			when grouping([DB-ID])=1 then ''
    			when grouping(ENTITY)=1 then ''
    			when grouping(DATE)=1 then ''
    			when grouping([CUST-NO])=1 then '小计'
    			else cast([CUST-NO] as varchar) end
    		,AMOUNT=sum(AMOUNT),TAX=sum(TAX)
    		,s1=grouping([DB-ID]),s2=[DB-ID]
    		,s3=grouping(ENTITY),s4=ENTITY
    		,s5=grouping(DATE),s6=DATE
    		,s7=grouping([CUST-NO]),s8=[CUST-NO]
    	from tb
    	group by [DB-ID],ENTITY,DATE,[CUST-NO] with rollup
    	having grouping([CUST-NO])=1
    	union all
    	select [DB-ID],ENTITY,DATE,cast([CUST-NO] as varchar),AMOUNT,TAX
    		,s1=0,s2=[DB-ID]
    		,s3=0,s4=ENTITY
    		,s5=0,s6=DATE
    		,s7=0,s8=[CUST-NO]
    	from tb 
    )a order by s1,s2,s3,s4,s5,s6,s7,s8
    go
    
    --删除测试
    drop table tb
    
    /*--测试结果
    DB-ID	ENTITY	DATE		CUST-NO	AMOUNT	TAX
    -------- -------- ----------------- --------- -------- ---------
    RCHQ	001	2004-11-10	200000	100.00	17.00
    RCHQ	001	2004-11-10	200000	200.00	34.00
    RCHQ	001	2004-11-10	小计	300.00	51.00
    RCHQ	001	2004-11-12	200000	150.00	25.50
    RCHQ	001	2004-11-12	小计	150.00	25.50
    RCHQ	001	小计			450.00	76.50
    RCHQ	002	2004-11-10	200000	100.00	17.00
    RCHQ	002	2004-11-10	200000	200.00	34.00
    RCHQ	002	2004-11-10	小计	300.00	51.00
    RCHQ	002	2004-11-12	200000	150.00	25.50
    RCHQ	002	2004-11-12	小计	150.00	25.50
    RCHQ	002	小计			450.00	76.50
    RCHQ	小计				900.00	153.00
    合计					900.00	153.00
    --*/