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
)

 

posted @ 2020-12-24 17:06  lucky8492  阅读(590)  评论(0编辑  收藏  举报