删除表中重复数据

1，删除表中重复数据，仅保留重复数据id最小的

代码语言：javascript复制

delete from yyd_wykl_goodsInfo
   
where skuId in (select skuId from (select skuId from yyd_wykl_goodsInfo group by skuId having count(skuId) > 1) a)
 
and id not in ( select id from (select min(id) as id  from yyd_wykl_goodsInfo group by skuId having count(skuId )>1) b);

2，查找表中多余的重复记录（多个字段），不包含id最小的记录

代码语言：javascript复制

select * from yyd_wykl_goodsInfo as s
where s.id,s.title in (select id,title from yyd_wykl_goodsInfo group by id,name  having count(id)>1) 
and s.id not in ( select min(id) as id  from yyd_wykl_goodsInfo group by id,name having count(id )>1);

3，查找表中多余的重复记录，重复记录是根据单个字段（userId）来判断

代码语言：javascript复制

select * from user
where userId in (select  userId from  usergroup  by  peopleId  having  count(userId) > 1)

4、查找表中多余的重复记录（多个字段）

代码语言：javascript复制

select * from user a
where (a.userId,a.name) in  (select userId,name from user group by userId,name having count(*) > 1)

count having min select 数据

0 人点赞