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