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);
更新字段
update cl_accrue_price set `status` = 0 WHERE (gateway_id, country_code_number) IN (select temp1.gateway_id, temp1.country_code_number from (SELECT gateway_id, country_code_number FROM cl_accrue_price GROUP BY gateway_id, country_code_number HAVING count(*) > 1 ) as temp1)
AND ac_price_id NOT IN (select * from(SELECT min(ac_price_id) FROM cl_accrue_price GROUP BY gateway_id, country_code_number HAVING count(*) > 1 ) as temp2);