Some complex sql statements for querying and deleting duplicate rows in Mysql

This article mainly introduces Mysql some complex sql statements (Query and Deleteduplicate rows), friends in need can refer to the following

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 (keep one)

PS: Because of mysql’s delete, if it is deleted There is in in the where condition of the table, and the table also exists in in, so it cannot be deleted.

/*创建个临时表*/
CREATE TABLE blog_user_relation_temp AS
(
 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)
 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 FROM `blog_user_relation` WHERE relation_id IN (SELECT relation_id FROM blog_user_relation_temp);

/*删除临时表*/
DROP TABLE blog_user_relation_temp;

The above is the detailed content of Some complex sql statements for querying and deleting duplicate rows in Mysql. For more information, please follow other related articles on the PHP Chinese website!