登录

mysql去重,为什么每次只能去掉几百或者几十条重复数据,明明有几十万条重复数据

需求:删除DRUG中同primaryid和drugname的项目,并保留drug_seq最小的一行

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

#方法一:

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);

# MySQL
P粉029305743P粉02930574331 天前262 次浏览

全部回复(0) 我要回复

暂无回复
  • 取消回复发送