删除重复记录的sql

比眉伴天荒 2022-02-04 05:51 318阅读 0赞

当数据库表里有重复记录时怎么删除重复的那些数据呢？

当然前提是重复的定义：在数据库表，我们认为某个或者某几个字段相同，那就认为这条记录重复了。

如下表：

<table> 
 <tbody> 
  <tr> 
   <th>ROWID</th> 
   <th>DEP_ID</th> 
   <th>DEP_NAME</th> 
   <th>COMPANY</th> 
  </tr> 
  <tr> 
   <td>AAARJ4AAEAAABeGAAA</td> 
   <td>13&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td> 
   <td>人事部</td> 
   <td>联想二部</td> 
  </tr> 
  <tr> 
   <td>AAARJ4AAEAAABeIAAA</td> 
   <td>11&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td> 
   <td>研发部</td> 
   <td>百度hahahaha</td> 
  </tr> 
  <tr> 
   <td>AAARJ4AAEAAABeIAAB</td> 
   <td>12&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td> 
   <td>人事部</td> 
   <td>联想一部</td> 
  </tr> 
 </tbody> 
</table>

在这张表中，我认为dep\_name重复的记录就是重复的。我想删除多余的人事部，只保留一个人事部。

sql如下：

select d.rowid from LSY\_DEPARTMENT d /\* delete from LSY\_DEPARTMENT d\*/  
where d.rowid in  
(  
select rowid from LSY\_DEPARTMENT c  
where c.dep\_name in  
(  
select a.dep\_name from LSY\_DEPARTMENT a  
where 1=1  
group by dep\_name  
having count(a.dep\_name)>1  
)  
) and d.rowid not in  
(  
select min(t.rowid) from LSY\_DEPARTMENT t  
where 1=1  
group by t.dep\_name  
having count(\*)>1  
)

<table> 
 <tbody> 
  <tr> 
   <th>ROWID</th> 
  </tr> 
  <tr> 
   <td>AAARJ4AAEAAABeIAAB</td> 
  </tr> 
 </tbody> 
</table>

把上面第一行改成delete即可。