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
posted @   林暗惊风  阅读(126)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示