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
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战