MySQL 从一个表中查询最近的记录，但从另一个表中按顺序查询

Question

对 mysql 来说真的很新。我正在尝试从 table2 查询以显示每个用户的最新登录，但我想按 table1 中的 uid 顺序显示结果。我正在使用从另一个线程中找到的查询，但它显示的结果基于时间戳而不是 uid:

SELECT * 
FROM (SELECT * FROM table2 ORDER BY timestamp DESC) AS x
GROUP BY username

这是我正在寻找的表格和结果:

表1

------------------
| uid | username |
------------------
|  1  | user1    |
------------------
|  2  | user2    |
------------------
|  3  | user3    |
------------------
|  4  | user4    |
------------------

表2

--------------------------------------------
|     timestamp        | username | logins |
--------------------------------------------
| 2014-02-18 23:29:31  | user2    | OUT    |
--------------------------------------------
| 2014-02-18 23:28:31  | user2    | IN     |
--------------------------------------------
| 2014-02-18 23:27:31  | user3    | OUT    |
--------------------------------------------
| 2014-02-18 23:26:31  | user1    | IN     |
--------------------------------------------
| 2014-02-18 23:25:31  | user2    | OUT    |
--------------------------------------------
| 2014-02-18 23:24:31  | user3    | IN     |
--------------------------------------------

我希望得到这样的结果

--------------------------------------------
|  username |      timestamp      | logins |
--------------------------------------------
|  user1    | 2014-02-18 23:26:31 | IN     |
--------------------------------------------
|  user2    | 2014-02-18 23:29:31 | OUT    |
--------------------------------------------
|  user3    | 2014-02-18 23:27:31 | OUT    |
--------------------------------------------
|  user4    | null                | null   |
--------------------------------------------

我希望这已经足够清楚了。提前致谢!

Answer 1

试试这个:

SELECT `table2.username`, `table2.timestamps`, `table2.logins`
FROM `table2`
JOIN (
  SELECT MAX(`timestamp`) AS ts, `username`
  FROM `table2`
  GROUP BY `username`
) AS `t`
ON `t.username` = `table2.username` AND `t.ts` = `table2.timestamp`
RIGHT OUTER JOIN `table1`
ON `table1.username` = `table2.username`
ORDER BY `table1.uid`

首先，我有一个连接的 table2 和一个派生表 t。此派生表获取每个用户的最新时间戳。如果不使用此派生表，则无法获得 login 列的正确值。接下来，我对 table1 进行了 RIGHT JOIN 以获取 uid，以便它可以在 ORDER BY 中使用。