假设我表里有记录：
Aid AName Aoher
1 lily 2233
2 lucy 568
3 jack 589
4 lily 2233
5 david 25
6 lily 2233
7 lucy 568
8 lucy 568
9 jack 589
......
其中里面有重复的记录（除了aid不同外）
我要更新表使它变成：
Aid AName Aother
1 lily 2233
2 lucy 568
3 jack 589
1 lily 2233
5 david 25
1 lily 2233
2 lucy 568
2 lucy 568
3 jack 589
说明：即修改记录的编号为重复记录的的编号的较小者，如何用sql语言实现？
------解决方案--------------------
aid如果不要求唯一
- SQL code
update a set aid = (select min(aid) from tab where aname = a.aname and Aoher = a.Aoher)from tab a
------解决方案--------------------
update A
set A.Aid = B.Aid
from TableA A,
(
select min(Aid),AName,Aoher
from TableA
group by AName,Aoher
) B
where B.AName = A.AName and B.Aoher = A.Aoher
------解决方案--------------------
- SQL code
create table vic(Aid int, AName varchar(6), Aoher int)insert into vicselect 1, 'lily', 2233 union allselect 2, 'lucy', 568 union allselect 3, 'jack', 589 union allselect 4, 'lily', 2233 union allselect 5, 'david', 25 union allselect 6, 'lily', 2233 union allselect 7, 'lucy', 568 union allselect 8, 'lucy', 568 union allselect 9, 'jack', 589with t as(select row_number() over(partition by AName,Aoher order by Aid) rn,Aid,AName,Aoher from vic)update a set a.Aid=b.Aidfrom vic ainner join (select * from t where rn=1) b on a.AName=b.AName and a.Aoher=b.Aoherselect * from vicAid AName Aoher----------- ------ -----------1 lily 22332 lucy 5683 jack 5891 lily 22335 david 251 lily 22332 lucy 5682 lucy 5683 jack 589(9 row(s) affected)