如何将一个表中的多行数据合并为一行新数据？

[sbf2000]

比如有表A
ID　Field1 Field2 Field3
1 b 10.1 12.2
2 b 9.8 8.6
3 c 12.3 11.2
4 c 5.3 8.2
现在想生成下面这样的数据
ID　Field1 Field2 Field3
1 b 10.1 12.2
2 b 9.8 8.6
3 c 12.3 11.2
4 c 5.3 8.2
5　　b_all 19.9 20.8 注：19.9=10.1+9.8　20.8=12.2+8.6
6 c_all 17.6 19.4

请问如何用SQL语句或存贮过程来实现？

[txlicenhe]

Try:
Select * from 表A
Union all
Select 5,'b_all',sum(field2),sum(field3) from 表A where field1 = 'b'
group by 5,'b_all'
Union all
Select 6,'b_all',sum(field2),sum(field3) from 表A where field1 = 'c'
group by 5,'b_all'

[pengdali]

select * from a
union all
select 5,'b_all',sum(field2),sum(field3) from a where field1='b'
union all
select 6,'c_all',sum(field2),sum(field3) from a where field1='c'

[txlicenhe]

try:
Select * from 表A
Union all
Select 5 ,'b_all' ,sum(field2),sum(field3) from test where field1 = 'b'
Union all
Select 6,'c_all',sum(field2),sum(field3) from 表A where field1 = 'c'

[txlicenhe]

Select * from 表A
Union all
Select (select max(id) from 表A)+1 ,'b_all' ,sum(field2),sum(field3) from 表A where field1 = 'b'
Union all
Select (select max(id) from 表A)+2,'c_all',sum(field2),sum(field3) from 表A where field1 = 'c'

[yujohny]

select IDENTITY(INT,1,1) ID,T.* into #temp FROM
((select Field1,Field2,Field3 from 表A Union all
select Field1+'_all',sum(Field2),sum(Field3)
from 表A group by Field1)) T

select * from #temp

drop table #temp

[wzh1215]

Select * from 表A
Union all
Select (select max(id) from 表A)+1 ,'b_all' ,sum(field2),sum(field3) from 表A where field1 = 'b'
Union all
Select (select max(id) from 表A)+2,'c_all',sum(field2),sum(field3) from 表A where field1 = 'c' order by id

[txlicenhe]

测试:
create table 表A (id int,field1 char(1),field2 numeric(10,1),field3 numeric(10,1))
insert 表A select 1 ,'b', 10.1, 12.2
union all select 2 ,'b', 9.8, 8.6
union all select 3 ,'c', 12.3, 11.2
union all select 4 ,'c', 5.3, 8.2

Select * from 表A
Union all
Select (select max(id) from 表A)+1 ,'b_all' ,sum(field2),sum(field3) from 表A where field1 = 'b'
Union all
Select (select max(id) from 表A)+2,'c_all',sum(field2),sum(field3) from 表A where field1 = 'c'

id field1 field2 field3
----------- ------ ---------------------------------------- ----------------------------------------
1 b 10.1 12.2
2 b 9.8 8.6
3 c 12.3 11.2
4 c 5.3 8.2
5 b_all 19.9 20.8
6 c_all 17.6 19.4

（所影响的行数为 6 行）