Mysql必读Mysql一些复杂的sql语句(查询与删除重复的行)

《Mysql必读Mysql一些复杂的sql语句(查询与删除重复的行)》要点：
本文介绍了Mysql必读Mysql一些复杂的sql语句(查询与删除重复的行)，希望对您有用。如果有疑问，可以联系我们。

MYSQL教程1.查找重复的行

MYSQL教程
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)

MYSQL教程2.删除重复的行(保留一条)

MYSQL教程PS：因为mysql的delete,如果被删的表的where条件里有in,且in里面也有此表,那就删除不了.

MYSQL教程
/*创建个临时表*/
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 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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!