MySQL删除多个字段重复的数据，只留一条数据

MySQL删除多个重复的数据，只留一条数据

---

DELETE from jw_user_wechat
where (app_id,open_id) in (SELECT * FROM (select app_id,open_id from jw_user_wechat group by app_id,open_id having count(*) > 1) AS TEMP1)
and id not in (SELECT * FROM (select min(id) from jw_user_wechat where user_id is not null and user_id != '' group by app_id,open_id having count(*)>1) AS TEMP2)

分析：

这里用到了临时表，解决如下报错

Mysql: Table 'XXX' is specified twice, both as a target for 'DELETE' and as a separate source for data

第一步：先用 having count(*) 查出出现两次以上的数据

第二步：再not in 留下最小ID的，用户ID不为空的数据