删除具有相同 ID 的条目，但保留表中的最后一个 [英] DELETE FROM for entries with same id, but leave the last one in the table

问题描述

我有一张包含

的表格

id 用户名 密码 last_touch

同一个 ID 可能有重复的条目.last_touch 是最后一个插入命令的时间戳.除了剩下的最后一个条目之外，如何删除所有具有相同 ID 的条目，以便我始终拥有最新的用户详细信息?

某处:

从用户数据中删除哪里 id=1限制(计数(选择 1从用户数据哪里 id=1) - 1)

(显然上面例子中的语法不正确，所以 MySQL 会抱怨.)

解决方案

使用嵌套查询为给定的用户 id 选择最新的时间戳，并删除该 id 与时间戳不同的所有行:

从用户数据中删除WHERE ID = 1 AND last_touch !=(选择最新的发件人(SELECT MAX(last_touch) AS latest FROM user_data WHERE ID = 1) AS temp)

更新:修复了对内部 SELECT 中正在修改的表的直接引用，方法是将另一个 SELECT 包裹在它周围，根据 Frank 的评论.>

I have a table that contains

id  username    password    last_touch

It is possible to have duplicate entries for the same id. The last_touch is the timestamp of the last insert command. How can I delete all entries with the same id, apart from the last one that is left so I always have the user details which are most up to date?

Somewhere along the lines of:

DELETE FROM user_data 
WHERE id=1 
LIMIT (count(SELECT 1 
             FROM user_data 
             WHERE id=1) - 1)

(Obviously the syntax is not correct in the above example, so MySQL complains.)

解决方案

Use a nested query to select the latest timestamp for the given user id, and delete all rows for that id with timestamps different than that one:

DELETE FROM user_data
WHERE ID = 1 AND last_touch != 
    (SELECT latest FROM
        (SELECT MAX(last_touch) AS latest FROM user_data WHERE ID = 1) AS temp
    )

Update: Fixed direct reference to table being modified in inner SELECT by wrapping another SELECT around it, as per Frank's comments.

这篇关于删除具有相同 ID 的条目，但保留表中的最后一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！