Mysql 删除重复数据，保留一条有效数据

-- 方法一（笨方法但容易理解）

DELETE FROM t_user WHERE user_name IN (

SELECT t1.user_name FROM (

-- 查询出所有重复的user_name

SELECT user_name FROM t_user GROUP BY user_name HAVING COUNT(1)>1

) t1

)

AND id NOT IN (

SELECT t2.min_id FROM (

-- 查询出所有重复的记录并各自只取其中一条（MIN(id)或MAX(id)都可以）

SELECT MIN(id) AS min_id FROM t_user GROUP BY user_name HAVING COUNT(1)>1

) t2

)

-- 方法二（推荐方法也容易理解）

DELETE FROM t_user WHERE id NOT IN (

SELECT t.min_id FROM (

-- 过滤出重复多余的数据，比如，如果所有记录中存在1条记录是user_name=zhangsan的，那么就取出它；

    -- 如果所有记录中存在多条记录是user_name=lisi的，那么只取其中1条，其他的不查询出来

SELECT MIN(id) AS min_id FROM t_user GROUP BY user_name

  ) t

)

-- 方法三（推荐方法但不太容易理解）

DELETE FROM t_user WHERE id IN (

SELECT t.id FROM (

-- 1. 关于所有存在相同user_name的记录，只查询出（保留）重复记录中的1条，假设这样查询出来的集合为A集合。

-- 2. 在所有记录中，只要id不在A集合中的，都把它们查询出来

SELECT t1.id FROM t_user AS t1 WHERE t1.id <> (SELECT MAX(t2.id) FROM t_user AS t2 WHERE t1.user_name=t2.user_name)

) t

)

-- 或

DELETE FROM t_user t1

WHERE t1.id <> (

SELECT t2.max_id FROM (

SELECT MAX(t3.id) AS max_id FROM t_user t3 WHERE t1.user_name=t3.user_name

) t2

)