mysql保留一条数据删除其他相同的数据
数据库表:
需求:删除name相同的数据并保留其中的一条
-- 不指定是保留最大id或者最小id,默认保留最小的id
DELETE FROM `user` WHERE id NOT IN(
SELECT * FROM(
SELECT id FROM `user` GROUP BY `name`
)AS b
)
-- 保留的是最小的id
DELETE FROM `user` WHERE id NOT IN(
SELECT * FROM (
SELECT MIN(id) FROM `user` GROUP BY `name`
) AS a
)
-- 保留的是最大的id
DELETE FROM `user` WHERE id NOT IN(
SELECT * FROM (
SELECT MAX(id) FROM `user` GROUP BY `name`
) AS a
)
做个标记,方便以后忘记了。需要的别客气,拿走!