MySQL：获取最新记录
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8523374/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Get most recent record
提问by enchance
In the table below, how do I get just the most recent recordof id=1based on the signin column and not all 3 records?
在下面的表格，我怎么得到公正的最新记录的id=1基础上，登入列，而不是所有3条记录？
+----+---------------------+---------+
| 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 |
+----+---------------------+---------+
回答by Michael Berkowski
Use the aggregate MAX(signin)grouped by id. This will list the most recent signinfor each id.
使用MAX(signin)按 id 分组的聚合。这将列出signin每个id.
SELECT
id,
MAX(signin) AS most_recent_signin
FROM tbl
GROUP BY id
To get the whole single record, perform an INNER JOINagainst a subquery which returns only the MAX(signin)per id.
要获取整个单个记录，请INNER JOIN对仅返回MAX(signin)每个 id的子查询执行 an 。
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
回答by Erwin Brandstetter
SELECT *
FROM tbl
WHERE id = 1
ORDER BY signin DESC
LIMIT 1;
The obvious index would be on (id), or a multicolumn indexon (id, signin DESC).
明显的索引是 on (id)，或者是多列索引on (id, signin DESC)。
Conveniently for the case, MySQL sorts NULLvalues lastin descending order. That's what you typically want if there can be NULLvalues: the row with the latest not-null signin.
在这种情况下，MySQL 将NULL值按降序排列在最后。如果可以有NULL值，这就是您通常想要的：具有最新 not-null 的行signin。
To get NULLvalues first:
首先获取NULL值：
ORDER BY signin IS NOT NULL, signin DESC
Related:
有关的：
The SQL standard does not explicitly define a default sort order for NULLvalues. The behavior varies quite a bit across different RDBMS. See:
SQL 标准没有明确定义NULL值的默认排序顺序。行为在不同的 RDBMS 之间有很大差异。看：
But there arethe NULLS FIRST/ NULLS LASTclauses defined in the SQL standard and supported by most major RDBMS, but not by MySQL. See:
但也有是的NULLS FIRST/NULLS LAST由MySQL大多数主要RDBMS在SQL标准定义，并支持条款，但没有。看：
回答by rantsh
Building on @xQbert's answer's, you can avoid the subquery AND make it generic enough to filter by any ID
以@xQbert 的答案为基础，您可以避免使用子查询并使其足够通用以按任何 ID 进行过滤
SELECT id, signin, signout
FROM dTable
INNER JOIN(
SELECT id, MAX(signin) AS signin
FROM dTable
GROUP BY id
) AS t1 USING(id, signin)
回答by xQbert
Select [insert your fields here]
from tablename
where signin = (select max(signin) from tablename where ID = 1)
回答by user3044573
SELECT * FROM (SELECT * FROM tb1 ORDER BY signin DESC) GROUP BY id;
回答by Ales
I had a similar problem. I needed to get the last version of page content translation, in other words - to get that specific record which has highest number in version column. So I select all records ordered by version and then take the first row from result (by using LIMIT clause).
我有一个类似的问题。我需要获取页面内容翻译的最新版本，换句话说 - 获取版本列中编号最高的特定记录。所以我选择所有按版本排序的记录，然后从结果中取出第一行（通过使用 LIMIT 子句）。
SELECT *
FROM `page_contents_translations`
ORDER BY version DESC
LIMIT 1
回答by Ajmal Jamil
Simple Way To Achieve
简单的实现方式
I know it's an old question You can also do something like
我知道这是一个老问题你也可以做类似的事情
SELECT * FROM Table WHERE id=1 ORDER BY signin DESC
In above, query the first record will be the most recent record.
在上面，查询第一条记录将是最近的记录。
For only one record you can use something like
对于只有一个记录，您可以使用类似
SELECT top(1) * FROM Table WHERE id=1 ORDER BY signin DESC
Above query will only return one latest record.
以上查询只会返回一条最新记录。
Cheers!
干杯!
