MySQL删除重复数据
-
如下表
单子段重复
- 删除删除仅stu_name一个字段重复的数据
-- 查询全部重复数据 select * from tb_4 where stu_name in (select stu_name from tb_4 group by stu_name having count(1) > 1) -- 删除全部重复数据 delete from tb_4 where stu_name in (select a.stu_name from (select stu_name from tb_4 group by stu_name having count(1) > 1)a)
![在这里插入图片描述](https://img-blog.csdnimg.cn/c2bf098b61194e77acd54fe53a9f8ddc.png#pic_center)
- 部分删除(保留一行)
-- 查询多余重复数据 select * from tb_4 where id not in (select min(id) from tb_4 group by stu_name) -- 删除多余重复数据(保留一行) delete from tb_4 where id not in (select a.min_id from (select min(id) as min_id from tb_4 group by stu_name)a)
![在这里插入图片描述](https://img-blog.csdnimg.cn/bb698d67c0674e809f70d30f02b19c2a.png#pic_center)
多字段重复:
-
全部删除 (删除stu_name、score两个字段重复的数据)
-- 查询全部重复数据 select * from tb_4 where stu_name in (select a.stu_name from (select stu_name from tb_4 group by stu_name, score having count(1) > 1)a) and score in (select a.score from (select score from tb_4 group by stu_name, score having count(1) > 1)a) -- 删除全部重复数据 delete from tb_4 where stu_name in (select a.stu_name from (select stu_name from tb_4 group by stu_name, score having count(1) > 1)a) and score in (select a.score from (select score from tb_4 group by stu_name, score having count(1) > 1)a)
![在这里插入图片描述](https://img-blog.csdnimg.cn/c926658eb9bc47c0b4760d081aaa2005.png#pic_center)
-
部分删除(保留一行)
-- 查询多余重复数据 select * from tb_4 where id not in (select min(id) from tb_4 group by stu_name, score) -- 删除多余重复数据(保留一行) delete from tb_4 where id not in (select a.min_id from (select min(id) as min_id from tb_4 group by stu_name, score)a)
![在这里插入图片描述](https://img-blog.csdnimg.cn/f10f2284d0c449a19f667c54def40afe.png#pic_center)