如何从MySQL中的表中删除最后一条记录(条件)
问题描述：
我有一个 LoginTime 表，如下所示:
I have a LoginTime table like this:
id | user_id | datetime
1 | 1 | 2011-01-17 18:51:05
2 | 1 | 2011-01-18 18:51:05
3 | 1 | 2011-01-19 18:51:05
4 | 2 | 2011-01-19 18:51:05
我想删除user_id=1的最后一条记录.用户的最后一条记录可以由datetime识别.
I want to delete last record for user_id=1. Last record of a user can be recognized by datetime.
如何使用一个查询执行此操作.
How can I do this with one query.
答
您需要按user_id(例如WHERE user_id = 1)过滤表，然后按时间(例如ORDER BY datetime)对其进行排序，然后将查询限制为仅一项(例如LIMIT 1)，您将删除此查询的结果.最后，您将获得如下查询:
You need to filter the table by user_id (eg WHERE user_id=1), then sort it by time (eg ORDER BY datetime) and then limit the query to just one item (eg LIMIT 1) and you delete the result of this query. At the end youl get query like this:
DELETE FROM LoginTime WHERE user_id=1 ORDER BY datetime DESC LIMIT 1