获取给定ID的最新行 [英] Get most recent row for given ID

问题描述

在下表中，如何根据 signin 列仅获取最近行和 id = 1 全部三行?

In the table below, how do I get just the most recent row with id=1 based on the signin column, and not all 3 rows?

+----+---------------------+---------+
| 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 的最新登录.

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

这篇关于获取给定ID的最新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！