mysql 删除单表内多个字段重复的数据

最新推荐文章于 2024-10-23 09:16:57 发布

幻羽公子

最新推荐文章于 2024-10-23 09:16:57 发布

阅读量722

点赞数

分类专栏： Mysql 文章标签： mysql

本文链接：https://blog.youkuaiyun.com/huanyu12/article/details/79786094

版权

Mysql 专栏收录该内容

27 篇文章

订阅专栏

本文介绍如何使用SQL语句来查询并删除数据库表中重复的数据记录，包括基于多个字段的重复记录处理方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DELETE from lot_log_payflow WHERE (pay_no,sub_flow_type) in   
(SELECT pay_no,sub_flow_type from (SELECT pay_no,sub_flow_type FROM lot_log_payflow GROUP BY pay_no,sub_flow_type HAVING COUNT(*)>1) s1)   
AND  
id NOT in (SELECT id from (SELECT id FROM lot_log_payflow GROUP BY pay_no, sub_flow_type HAVING COUNT(*)>1) s2);

查询重复的数据

select * from lot_log_payflow  
WHERE (pay_no,sub_flow_type) in   
(SELECT pay_no,sub_flow_type from (SELECT pay_no,sub_flow_type FROM lot_log_payflow GROUP BY pay_no,sub_flow_type HAVING COUNT(*)>1) s1)   
AND  
id NOT in (SELECT id from (SELECT id FROM lot_log_payflow GROUP BY pay_no,sub_flow_type HAVING COUNT(*)>1) s2) order by create_time asc;

删除单个字段重复数据

DELETE from lot_order WHERE (order_no) in   
(SELECT order_no from (SELECT order_no FROM lot_order GROUP BY order_no HAVING COUNT(*)>1) s1)   
AND  
id NOT in (SELECT id from (SELECT id FROM lot_order GROUP BY order_no HAVING COUNT(*)>1) s2);