Sql 语句删除重复项,保留id最小项
select * from test
ID Name 1 a 2 b 3 a 4 b 5 c 6 d
-- --标准sql方法:mysql、sql server-- -- 查询重复 方法一 select t.id,t.name from test t join( select min(id) id,name from test group by name having(count(1)>1) ) tt on t.id>tt.id and t.name=tt.name -- 查询重复 方法二 select * from test where exists( select * from ( select min(id) id,name from test group by name having(count(1)>1) ) as tt where test.id>tt.id and test.name=tt.name ) -- 删除重复 方法 delete from test where exists( select * from ( select min(id) id,name from test group by name having(count(1)>1) ) as tt where test.id>tt.id and test.name=tt.name ) -- --sql server方法---- -- 查询 select * from ( select ROW_NUMBER() over(partition by name order by id ) row,id,name from test ) t where t.row>1 -- 删除 delete from test where exists( select * from ( select ROW_NUMBER() over(partition by name order by id ) row,id,name from test ) t where t.row>1 and test.id=t.id )