小计 合计

--测试数据
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
--*/