带小计和总计的SQL语句

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

--邹建