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

时间:2022-03-12 13:21:48

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

 

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;