本文介绍了MySQL:获取最新记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在下表中,如何仅根据登录列获取的最新记录,而不是全部3条记录?
In the table below, how do I get just the most recent record of id=1 based on the signin column and not all 3 records?
+----+---------------------+---------+ | id | signin | signout | +----+---------------------+---------+ | 1 | 2011-12-12 09:27:24 | NULL | | 1 | 2011-12-13 09:27:31 | NULL | | 1 | 2011-12-14 09:27:34 | NULL | | 2 | 2011-12-14 09:28:21 | NULL | +----+---------------------+---------+
推荐答案
使用按ID分组的汇总MAX(signin).这将列出每个id的最新signin.
Use the aggregate MAX(signin) grouped by id. This will list the most recent signin for each id.
SELECT id, MAX(signin) AS most_recent_signin FROM tbl GROUP BY id
要获取整个单个记录,请对子查询执行INNER JOIN,该子查询仅返回每个id MAX(signin).
To get the whole single record, perform an INNER JOIN against a subquery which returns only the MAX(signin) per id.
SELECT tbl.id, signin, signout FROM tbl INNER JOIN ( SELECT id, MAX(signin) AS maxsign FROM tbl GROUP BY id ) ms ON tbl.id = ms.id AND signin = maxsign WHERE tbl.id=1
这篇关于MySQL:获取最新记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!