SQL分类汇总

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
/*--结果
Groups Item       Color           Quantity   
-------- ---------------- ---------------------- -----------
aa     Chair      Blue            101
aa     Chair      Red             -90
                 Chair 小计       11
aa     Table      Blue            124
                 Table 小计       124
       aa 合计                    135
bb     Cup        Green           -23
                  Cup 小计        -23
bb     Table      Red             -23
                 Table 小计       -23
       bb 合计                    -46
总计                              89
--*/

 

－－－－－－－－－－－－－－－－－－－－－－－－－－

 

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([部门] varchar(6),[电话] varchar(20),[金额] int)
insert [tb]
select '营业部',8001,20 union all
select '营业部',8002,30 union all
select '财务部',6001,10 union all
select '财务部',6003,100

select
  isnull(部门,'总计') as 部门,
  isnull(电话,'小计') as 电话,
  sum(金额) as 金额
from tb
group by 部门,电话 with rollup --测试结果:
/*
部门     电话                   金额         
------ -------------------- -----------
财务部    6001                 10
财务部    6003                 100
财务部    小计                   110
营业部    8001                 20
营业部    8002                 30
营业部    小计                   50
总计     小计                   160

（所影响的行数为 7 行）

*/