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

[姚海平]

需求：删除表格（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);

 

[CSDN-Ada助手]

您可以前往 CSDN问答-大数据 发布问题, 以便更快地解决您的疑问