Mysql一些复杂的sql语句(查询与删除重复的行)
1.查找重复的行
SELECT*FROMblog_user_relationaWHERE(a.account_instance_id,a.follow_account_instance_id) IN(SELECTaccount_instance_id,follow_account_instance_idFROMblog_user_relationGROUPBYaccount_instance_id,follow_account_instance_idHAVING COUNT(*)>1)
2.删除重复的行(保留一条)
PS:因为mysql的delete,如果被删的表的where条件里有in,且in里面也有此表,那就删除不了。
/*创建个临时表*/ CREATETABLEblog_user_relation_tempAS ( SELECT*FROMblog_user_relationaWHERE (a.account_instance_id,a.follow_account_instance_id) IN(SELECTaccount_instance_id,follow_account_instance_idFROMblog_user_relationGROUPBYaccount_instance_id,follow_account_instance_idHAVINGCOUNT(*)>1) AND relation_id NOTIN(SELECTMIN(relation_id)FROMblog_user_relationGROUPBYaccount_instance_id,follow_account_instance_idHAVINGCOUNT(*)>1)); /*删除数据*/ DELETEFROM`blog_user_relation`WHERErelation_idIN(SELECTrelation_idFROMblog_user_relation_temp); /*删除临时表*/ DROPTABLEblog_user_relation_temp;
热门推荐
1 训练狗狗憋尿
2 狗狗追踪训练
3 狗狗训练要多少钱
5 深圳狗狗训练
6 训练狗狗唤回
7 狗狗叫怎么训练
8 狗狗训练班
10 狗狗社会化训练
11 狗狗叫回训练
12 广州狗狗训练学校
13 狗狗训练躺下
14 狗狗随行训练
15 训练狗狗巡回
16 狗狗要怎么训练
17 狗狗喂食训练
18 训练狗狗躺下