mysql - 这种情况需要一条SQL查询语句

Question

这是表的结构

mysql> select * from p1;
+----------+-------+-----------+
| username | token | last_used |
+----------+-------+-----------+
| a        | aacs  |         5 |
| d        | dddd  |         3 |
| a        | aaaaa |         3 |
+----------+-------+-----------+

如何从用户名查询的集合中删除last_used值最小的记录？在这种情况下，给定的用户名参数是'a'，那么我应该删除记录'a','aaaaa',3，因为3小于5(last_used(5)的用户名是'a'，也)。

我的答案是

delete from persistent_logins 
where last_used=(select * 
                 from (select MIN(last_used) 
                       from persistent_logins where username=?)as t
                 ) 
  and username=?

但它很有用，我需要一个类似 delete .... from..where username=? 的语句重要的是只允许一个参数。但我的答案有两个参数。

Answer 1

您实际上可以将 ORDER BY 和 LIMIT 与 DELETE 一起使用。行将按该顺序删除，LIMIT 控制删除的行数。

If the ORDER BY clause is specified, the rows are deleted in the order that is specified. The LIMIT clause places a limit on the number of rows that can be deleted.

_{(来自:https://dev.mysql.com/doc/refman/5.0/en/delete.html)}

尝试这样的事情:

DELETE FROM p1
WHERE username = ?
ORDER BY last_used ASC
LIMIT 1