Mysql complex SQL statements (query and delete duplicate rows), mysqlsql

Mysql complex SQL statements (query and delete duplicate rows), mysqlsql

1. Find duplicate rows

SELECT * FROM blog_user_relation a WHERE (a.account_instance_id,a.follow_account_instance_id) IN (SELECT account_instance_id,follow_account_instance_id FROM blog_user_relation GROUP BY account_instance_id, follow_account_instance_id HAVING COUNT(*) > 1)

2. Delete duplicate rows (retain one row)

PS: Because of mysql's delete, if the where condition of the table to be deleted contains in, and in contains this table, it cannot be deleted.

/* CREATE temporary tables */create table blog_user_relation_temp AS (SELECT * FROM blog_user_relation a WHERE (. account_instance_id,. statement) IN (SELECT account_instance_id, distinct FROM distinct group by account_instance_id, distinct having count (*)> 1) AND relation_id not in (select min (relation_id) FROM blog_user_relation group by account_instance_id, follow_account_instance_id having count (*)> 1);/* DELETE data */delete from 'blog _ user_relation 'WHERE relation_id IN (SELECT relation_id FROM blog_user_relation_temp ); /* Delete temporary TABLE */drop table blog_user_relation_temp;