关于网友提出的“sql语句集合 |M| 求一条SQL更新表的语句 将相同的记录进行合并”问题疑问，本网通过在网上对“sql语句集合 |M| 求一条SQL更新表的语句 将相同的记录进行合并”有关的相关答案进行了整理，供用户进行参考，详细问题解答如下：
问题：sql语句集合 |M| 求一条SQL更新表的语句 将相同的记录进行合并
描述: 如我有表
A
id user shop count tatol
1 Mary pan 21 42
2 Mary pan 10 20
3 JiM pan 10 20
4 Jim bike 1 100
5 Jim bike 10 1000
然后要把表中相同user 相同 shop 的 count 和 tatol 进行合并结果表为
A
id user shop count tatol
1 Mary pan 31 62
3 JiM pan 10 20
4 Jim bike 11 1100
谢谢
解决方案1: mark.....
解决方案2: blackant2(乔峰) ( ) 信誉：100 Blog
=
可行,
解决方案3: ^_^
解决方案4: declare @tablename table
([id] int,[user] char(10),shop char(10),[count] int,[tatol] int)
insert into @tablename
select 1,'Mary', 'pan', 21,42
union select 2,'Mary' ,'pan' ,10 ,20
union select 3, 'JiM', 'pan' ,10 ,20
union select 4, 'Jim', 'bike' ,1 ,100
union select 5, 'Jim', 'bike' ,10, 1000
update a
set [count]=b.[count],tatol=b.tatol
from @tablename a
inner join (
select min([id]) as [id],[user],shop,sum([count]) as [count],sum( tatol) as tatol
from @tablename
group by [user],shop) b on a.id=b.id
delete from @tablename
where [id] not in (select min([id]) from @tablename group by [user],shop)
select * from @tablename
1 Mary pan 31 62
3 JiM pan 10 20
4 Jim bike 11 1100
解决方案5: update a
set count=b.count,tatol=b.tatol
from tablename a
inner join (
select min(id),user,shop,sum(count),sum( tatol)
from tablename
group by user,shop) b on a.id=b.id
delete from tablename
where id not in (select min(id) from tablename group by user,shop)
解决方案6: 类似的，把set语句都去掉试一下。
解决方案7: 另外创建一个和tablename一样结构的表：temptablename，然后执行：
delete from [temptablename]
set IDENTITY_INSERT temptablename ON
insert into [temptablename] (id, [user], shop, [count], tatol)select min(id),[user],shop,sum([count]),sum(tatol)
from tablename
group by [user],shop
set IDENTITY_INSERT temptablename Off
delete from tablename where id in (select id from tablename)
set IDENTITY_INSERT tablename ON
insert into tablename (id, [user], shop, [count], tatol) select * from temptablename
set IDENTITY_INSERT tablename Off
select * from tablename
不一定是最优的，但是work而且逻辑简单。
解决方案8: up
解决方案9: 一条语句有点困难，因为你至少要delete被合并的记录。
解决方案10: 又是你?
解决方案11: select min(id),user,shop,sum(count),sum( tatol)
from tablename
group by user,shop
以上介绍了“sql语句集合 |M| 求一条SQL更新表的语句 将相同的记录进行合并”的问题解答，希望对有需要的网友有所帮助。
本文网址链接：http://www.codes51.com/itwd/3581011.html
