Mysql去重复数据

"""根据url列查询重复的数据(根据单列判断重复)"""

# 1、找到url列条数大于1的重复记录
select url from GD_ClinicalTrailDetails group by url  having count(url) > 1

# 2、根据  url 列查询全部重复的数据

select 
　　*  
from 
　　GD_ClinicalTrailDetails 
where  
　　url  
in (select url from GD_ClinicalTrailDetails group by url  having count(url) > 1) 


# 3. 删除表中的重复记录:（根据单列删除且保留ID最小的一条）

DELETE
　　FROM GD_ClinicalTrailDetails
WHERE 
    url IN(SELECT
           url
           FROM (select url from GD_ClinicalTrailDetails group by url  having count(url) > 1) a)
    AND id NOT IN(SELECT * FROM (SELECT ID FROM GD_ClinicalTrailDetails GROUP BY url HAVING COUNT(url) >1) b);

# 4. 删除表中的重复记录:（根据多列删除且保留ID最大的一条）

DELETE

FROM 2_fdaAll
WHERE (primaryid,caseid,pt)IN(SELECT
primaryid,caseid,pt
FROM (SELECT primaryid,caseid,pt FROM 2_fdaAll GROUP BY primaryid,caseid,pt HAVING COUNT(*) > 1) a)


AND id NOT IN(SELECT
id
FROM (SELECT
max(id) AS id
FROM 2_fdaAll
GROUP BY primaryid,caseid,pt
HAVING COUNT(*) > 1) b)

参考来源：https://blog.csdn.net/to_real/article/details/124276968