Some complex sql statements of Mysql (query and delete duplicate rows)

This article mainly introduces some complex sql statements of Mysql (querying and deleting duplicate 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 delete, if there is in in the where condition of the deleted table, and there is also this table in in, Then 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 of Mysql (query and delete duplicate rows). For more information, please follow other related articles on the PHP Chinese website!