SQL删除重复数据的方法

骑猪看日落 2022-06-16 09:51 183阅读 0赞

例如：   
id           name         value   
1               a                 pp   
2               a                 pp   
3               b                 iii   
4               b                 pp   
5               b                 pp   
6               c                 pp   
7               c                 pp   
8               c                 iii   
id是主键   
要求得到这样的结果   
id           name         value   
1               a                 pp   
3               b                 iii   
4               b                 pp   
6               c                 pp   
8               c                 iii

方法1  
delete   表名     
where   id   not   in   (   
select   max(id)   from  表名     
group   by   name,value)   
  
方法2  
delete   a   
from  表名  a   left   join(   
select   min(id) as id   from  表名   group   by   name,value   
)b   on   a.id=b.id   
where   b.id   is   null

查询及删除重复记录的SQL语句

1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断  
select \* from people  
where peopleId in (select   peopleId from   people group by   peopleId having count(peopleId) > 1)  
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录  
delete from people  
where peopleId in (select   peopleId from people group by   peopleId   having count(peopleId) > 1)  
and rowid not in (select min(rowid) from   people group by peopleId having count(peopleId )>1)  
3、查找表中多余的重复记录（多个字段）  
select \* from vitae a  
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(\*) > 1)  
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录  
delete from vitae a  
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(\*) > 1)  
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(\*)>1)

5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录  
select \* from vitae a  
where (a.peopleId,a.seq) in   (select peopleId,seq from vitae group by peopleId,seq having count(\*) > 1)  
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(\*)>1)

转载原文的地址：[http://www.cnblogs.com/sunxiaonan/archive/2009/11/24/1609439.html][http_www.cnblogs.com_sunxiaonan_archive_2009_11_24_1609439.html]

[http_www.cnblogs.com_sunxiaonan_archive_2009_11_24_1609439.html]: http://www.cnblogs.com/sunxiaonan/archive/2009/11/24/1609439.html