<pre name="code" class="html">1.sql语句,原理使用临时表法,效率比较低
delete
FROM `source`
WHERE is_deleted=0
AND (student_id,score_id) IN
(SELECT f.student_id ,
f.score_id
FROM
(SELECT b.`student_id` AS student_id ,
b.`score_id` AS score_id
FROM `source` b
WHERE b.`is_deleted`=0
GROUP BY b.student_id,b.score_id
HAVING count(*) > 1) f )
AND id NOT IN
(SELECT g.id
FROM
(SELECT min(c.id) AS id
FROM `source` c
WHERE c.`is_deleted`=0
GROUP BY c.`student_id`,c.`score_id`
HAVING count(*)>1) g );
2.临时表法-需要dba协作
1.停服情况下,原表(备份),建一张临时表,将原表的唯一数据拷贝到临时表中,删除源表,更改临时表表名
2.非停服情况下去重,新建一张临时表
insert into temp () values (select distinct * from source)
delete from source where 重复数据 in (select * from temp)
insert into source () values(select * from temp)
解决mysql数据重复问题-只保留一条数据
最新推荐文章于 2024-07-11 10:27:20 发布