请教数据库查询问题！！！
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 行受影响)