这组数据如何统计？进来看看。。。

[FSU]

表 a
a b c d(字段)
a 1 1 1
b 2 2 2
c 3 3 3
d 2 32 32
e 21 12 1
f 12 2 1
g 1 21
h 2 32 9
..

要求每三行统计一次，其中a 字段不重复，得到结果应该是



a b c d(字段)
a 1 1 1
b 2 2 2
c 3 3 3
小计 6 6 6
d 2 32 32
e 21 12 1
f 12 2 1
小计 35 46 34
g 1 21
h 2 32 9
小计 3 53 9
合计 44 105 49

[playwarcraft]

--try:
create table tab(a char(04),b int,c int,d int)
insert into tab
select 'a',1,1,1 union all
select 'b',2,2,2 union all
select 'c',3,3,3 union all
select 'd',2,32,32 union all
select 'e',21,12,1 union all
select 'f',12,2,1 union all
select 'g',1,21,0 union all
select 'h',2,32,9

select id=identity(int,0,1),* into #t1 from tab
select a,b,c,d
from
(select id/3 as id ,a,b,c,d from #t1
union all
select id/3,'小計',sum(b),sum(c),sum(d) from #t1 group by id/3
union all
select null,'合計',sum(b),sum(c),sum(d) from #t1) T

order by case when id is null then 1 else 0 end,
id,
case a when '合計' then 2
when '小計' then 1
else 0 end

drop table tab,#t1

a b c d
---- ----------- ----------- -----------
a 1 1 1
b 2 2 2
c 3 3 3
小計 6 6 6
d 2 32 32
e 21 12 1
f 12 2 1
小計 35 46 34
g 1 21 0
h 2 32 9
小計 3 53 9
合計 44 105 49