mysql 删除子查询中的记录语句

最新推荐文章于 2023-07-25 00:03:10 发布

浪流

最新推荐文章于 2023-07-25 00:03:10 发布

阅读量667

点赞数

CC 4.0 BY-SA版权

文章标签： mysql

本文链接：https://blog.youkuaiyun.com/xiushan/article/details/84496357

本文介绍了如何使用SQL语句来查找并删除数据库表中的重复记录。包括针对单个字段及多个字段的重复记录处理方法，并提供了具体的SQL实现案例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >


delete from t_5star where  locationid in  (
      select e.locationid  from  (
            select a.*  from t_5star as a ,
            t_als_data as b where a.term =  b.term ) e 
)

关键是子查询中的语句要放到单一e的表中

查询及删除重复记录的方法
(一)
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)

(二)
比方说
在A表中存在一个字段“name”，
而且不同记录之间的“name”值有可能会相同，
现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
Select Name,Count(*) From A Group By Name Having Count(*) > 1

如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1