SQL分类汇总

[hzliuhai88]

如下数据：
ID DT SL
101 2013-02-01 3
101 2013-02-03 5
101 2013-02-05 3
102 2013-02-01 3
102 2013-02-01 3
需显示如下
ID DT SL
101 2013-02-01 3
101 2013-02-03 5
101 2013-02-05 3
合计：
102 2013-02-01 3
102 2013-02-03 3
合计： 6

[唐诗三百首]

create table hz
(ID int, DT varchar(16), SL int)

insert into hz
 select 101, '2013-02-01', 3 union all
 select 101, '2013-02-03', 5 union all
 select 101, '2013-02-05', 3 union all
 select 102, '2013-02-01', 3 union all
 select 102, '2013-02-01', 3
 

select case when rn is not null then rtrim(ID)
            else '合计:' end 'ID',
       case when rn is null then '' else max(DT) end 'DT',
       sum(SL) 'SL'
from (select ID,DT,SL,
             row_number() over(partition by ID order by getdate()) 'rn' from hz) a 
group by ID,rn
with rollup
having grouping(ID)<>1

/*
ID           DT               SL
------------ ---------------- -----------
101          2013-02-01       3
101          2013-02-03       5
101          2013-02-05       3
合计:                          11
102          2013-02-01       3
102          2013-02-01       3
合计:                          6

(7 row(s) affected)
*/

[xxfvba]

select * from hz union all select ID,'合计',sum(sl) from hz group by ID order by ID,DT