有这样一个表
declare @a table(a varchar(10),b varchar(20),c int)insert @a select 'A' ,'T',5
union all select 'A' ,'T',8
union all select 'B' ,'S',10如何合并字段a和b相同的C值。并删除其中一行。得出如下结果?SELECT * FROM @a
/*
a b c
A T 13
B S 10
*/
declare @a table(a varchar(10),b varchar(20),c int)insert @a select 'A' ,'T',5
union all select 'A' ,'T',8
union all select 'B' ,'S',10如何合并字段a和b相同的C值。并删除其中一行。得出如下结果?SELECT * FROM @a
/*
a b c
A T 13
B S 10
*/
union all select 'A' ,'T',8
union all select 'B' ,'S',10SELECT a,b,sum(c) as c FROM @a group by a,b/*
a b c
B S 10
A T 13
*/
if exists(select 1 from sys.tables where name='aaaaaaa')
drop table aaaaaaa
declare @a table(a varchar(10),b varchar(20),c int)insert @a select 'A' ,'T',5
union all select 'A' ,'T',8
union all select 'B' ,'S',10SELECT a,b,sum(c) as c into aaaaaaa FROM @a group by a,b
delete @a
insert into @a select * from aaaaaaaselect * from @a
select a,b,sum(c) c from @a group by a,b
union all select 'A' ,'T',8
union all select 'B' ,'S',10;with B AS (
select a, b, sum(c) as c, min(id) as mid
from @a
group by a, b
)
update @a set c=(select top 1 c from B where mid=id)delete from @a where c is nullselect * from @a
declare @a table(a varchar(10),b varchar(20),c int,d char(10),e varchar(20))insert @a select 'A' ,'T',5,'AB','DD'
union all select 'A' ,'T',8,'AB','DD'
union all select 'B' ,'S',10,'CD','EE'--如何合并相同字段a和b的C值。并删除其中一行。得出如下结果?SELECT * FROM @a
/*
a b c d e
A T 13 AB DD
B S 10 CD EE
*/
union all select 'A' ,'T',8
union all select 'B' ,'S',10--合并C值
;with B AS (
select a, b, sum(c) as c, min(id) as mid
from @a
group by a, b
)
update @a set c=(select top 1 c from B where mid=id)--删除重复的
delete from @a where c is nullselect * from @a