mysql用这几种方法去重,为什么每次只能去掉几百或者几十条重复数据,跟挤牙膏一样,每执行一下,去重一点,明明有几十万条重复数据,几种方式都试了

姚海平 2022-09-05 14:44:23

需求:删除表格(drug2022)中同primaryid和drugname的项目,并保留drug_seq最小的一行
#方法一:

DELETE FROM `drug2022` WHERE drug_seq IN(
    SELECT drug_seq FROM (
SELECT drug_seq FROM `drug2022` WHERE (primaryid,drugname) IN (SELECT primaryid,drugname FROM `drug2022` GROUP BY primaryid,drugname HAVING COUNT(*) > 1)
        AND
        drug_seq NOT IN (SELECT MIN(drug_seq) FROM `drug2022` GROUP BY primaryid,drugname HAVING COUNT(*) > 1))AS a1);

#方法二:

DELETE
FROM `drug2022`
WHERE drug_seq NOT IN (
    (SELECT t1.min_drug_seq
     FROM (SELECT MIN(drug_seq) AS min_drug_seq FROM `drug2022` GROUP BY drugname, primaryid HAVING COUNT(1) > 1) t1))
  AND (drugname, primaryid) IN
      (SELECT t2.drugname, t2.primaryid
       FROM (SELECT drugname, primaryid FROM `drug2022` GROUP BY drugname, primaryid HAVING COUNT(1) > 1) t2);

#方法三:

DELETE t1
FROM `drug2022` t1,
     `drug2022` t2
WHERE t1.primaryid = t2.primaryid
  AND t1.drugname = t2.drugname
  AND t1.drug_seq < t2.drug_seq;

  #方法四

DELETE
FROM `drug2022`
WHERE drug_seq NOT IN (SELECT * FROM (SELECT MIN(drug_seq) FROM `drug2022` GROUP BY primaryid, drugname) t2);

 

...全文
97 回复 打赏 收藏 举报
写回复
回复
切换为时间正序
请发表友善的回复…
发表回复
发帖
MySQL

5.6w+

社区成员

MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
帖子事件
编辑了帖子 (查看)
2022-09-05 14:46
编辑了帖子 (查看)
2022-09-05 14:45
创建了帖子
2022-09-05 14:44
社区公告
暂无公告