这个sql语句该怎么写？
表a
code name num
a a 3
b a 5
c d 6
d d 7
e e 4
在表a中，有这样的数据，现在要将相同name的code也改成相同的，然后把他们的num相加，即要得到这样的结果：
code name num
a a 8
c d 13
e e 4
该怎么操作呢？
[解决办法]
select name , min(code) code, sum(num) num from tb group by name
[解决办法]
update t
set
num=(select sum(num) from a where name=t.name)
from
a t
where
t.code=t.name
delete a where code <> name
[解决办法]
select code=min(code),[name],num=sum(num)
from 表a
group by [name]
[解决办法]
select name as code name,sum(num) from a group by a.name
[解决办法]
理解错了，修改一下：
update t
set
num=(select sum(num) from a where name=t.name)
from
a t
delete t from a t where exists(select 1 from a where name=t.name and code <t.code)
[解决办法]
create table tb(code varchar(10),name varchar(10),num int)
insert into tb values( 'a ', 'a ', 3)
insert into tb values( 'b ', 'a ', 5)
insert into tb values( 'c ', 'd ', 6)
insert into tb values( 'd ', 'd ', 7)
insert into tb values( 'e ', 'e ', 4)
select name , min(code) code, sum(num) num from tb group by name
drop table tb
/*
name code num
---------- ---------- -----------
a a 8
d c 13
e e 4
（所影响的行数为 3 行）
*/
[解决办法]
select name as First(code, "name ") name,sum(num) from a group by name
[解决办法]
--如果按照code的先后顺序，则可以像下面这样
declare @table table(code varchar(10),[name] varchar(10),num int)
insert into @table
select 'g ', 'a ',3
union all select 'b ', 'a ',5
union all select 'c ', 'd ',6
union all select 'd ', 'd ',7
union all select 'e ', 'e ',4
union all select 'a ', 'e ',4
union all select 'f ', 'c ',4
select b.code,b.name,sum(b.num)as num from
(select code=(case when exists(select 1 from @table where [name]=a.[name]and code <a.code)then
(select top 1 code from @table where [name]=a.[name]and code <a.code order by code)
else code end),[name],num
from @table a)b
group by b.code,b.name
order by b.code,b.name
/*结果
code name num
---------- ---------- -----------
a e 8
b a 8
c d 13
f c 4
*/
