這篇文章主要介紹了mysql一些復雜的sql語句(查詢與刪除重復的行),需要的朋友可以參考下
1.查找重復的行
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.刪除重復的行(保留一條)
PS：因為mysql的delete，如果被刪的表的where條件里有in，且in里面也有此表，那就刪除不了。
/*創建個臨時表*/ 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 END
