ID NAME
100 'AAA '
101 'BBB '
102 'CCC '
103 'DDD '
100 'EEE '
100 'FFF '
101 'GGG '
查此表的重复ID，去除ID的纪录，只保留一个
------解决方案--------------------
ID NAME
100 'AAA '
101 'BBB '
102 'CCC '
103 'DDD '
100 'EEE '
100 'FFF '
101 'GGG '
查此表的重复ID，去除ID的纪录，只保留一个
--保留最小
select ID,min(NAME) name from tb group by id
--保留最大
select ID,max(NAME) name from tb group by id
------解决方案--------------------
declare @ta table(ID int, NAME varchar(10))
insert @ta select 100, 'AAA '
insert @ta select 101, 'BBB '
insert @ta select 102, 'CCC '
insert @ta select 103, 'DDD '
insert @ta select 100, 'EEE '
insert @ta select 100, 'FFF '
insert @ta select 101, 'GGG '
delete a
from @ta a
where exists(select 1 from @ta where id=a.id and name> a.name) --保留大的
select * from @ta
ID NAME
----------- ----------
102 CCC
103 DDD
100 FFF
101 GGG
(4 行受影响)
------解决方案--------------------
declare @ta table(ID int, NAME varchar(10))
insert @ta select 100, 'AAA '
insert @ta select 101, 'BBB '
insert @ta select 102, 'CCC '
insert @ta select 103, 'DDD '
insert @ta select 100, 'EEE '
insert @ta select 100, 'FFF '
insert @ta select 101, 'GGG '
delete a
from @ta a
where
exists(select 1 from @ta where id=a.id and name <a.name) --保留小的
select * from @ta
ID NAME
----------- ----------
100 AAA
101 BBB
102 CCC
103 DDD
(4 行受影响)