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 行受影响)
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' --如果只是查询: select * from @ta a where not exists(select 1 from @ta where id=a.id and name>a.name) --保留大的select * from @ta a where not exists(select 1 from @ta where id=a.id and name<a.name) --保留小的(1 行受影响) ID NAME ----------- ---------- 102 CCC 103 DDD 100 FFF 101 GGG(4 行受影响)ID NAME ----------- ---------- 100 AAA 101 BBB 102 CCC 103 DDD(4 行受影响)
select ID,min([name]) as [name]
from 表名
group by ID
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
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 行受影响)
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 行受影响)
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'
--如果只是查询:
select *
from @ta a
where not exists(select 1 from @ta where id=a.id and name>a.name) --保留大的select *
from @ta a
where
not exists(select 1 from @ta where id=a.id and name<a.name) --保留小的(1 行受影响)
ID NAME
----------- ----------
102 CCC
103 DDD
100 FFF
101 GGG(4 行受影响)ID NAME
----------- ----------
100 AAA
101 BBB
102 CCC
103 DDD(4 行受影响)