mysql - 查询以根据结果从一个表中选择然后从另一个表中选择

Question

我一直在尝试从一个表中选择所有内容，然后使用 MySQL 为每个结果从另一个表中只选择一个最新结果。

第一个表是标准表，有AI id和文本名称

users
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Peter |
+----+-------+

然后是第二个有AI id, int user_id, text action and datetime date

actions
+----+---------+--------+------------+-----+
| id | user_id | action |    date    | str |
+----+---------+--------+------------+-----+
|  7 |       2 | drink  | 2019-01-10 |   5 |
|  6 |       1 | sleep  | 2019-02-14 |  -2 |
|  5 |       2 | walk   | 2019-04-24 |   4 |
|  4 |       1 | jump   | 2019-03-14 |   3 |
|  3 |       2 | talk   | 2019-04-30 |  -8 |
|  2 |       2 | train  | 2019-04-14 |  -1 |
|  1 |       1 | drive  | 2019-04-01 |   1 |
+----+---------+--------+------------+-----+

现在我想从 table_users 中选择所有内容，并为每个找到的行搜索 table_actions 并根据 ID 或日期仅查找最新的行。

所以它看起来要么像这样(按 id)

[0] => ['user_id'] = 1
       ['name'] = John
       ['action'] = sleep
       ['date'] = 2019-02-14
       ['str'] = -2

[1] => ['user_id'] = 2
       ['name'] = Peter
       ['action'] = drink
       ['date'] = 2019-01-10
       ['str'] = 5

或者像这样

[0] => ['id'] = 1
       ['name'] = John
       ['table_actions'] => ['id'] = 6
                            ['user_id'] = 1
                            ['action'] = sleep
                            ['date'] = 2019-02-14
                            ['str'] = -2

这听起来很简单，但我尝试了一些东西，但没有任何效果像这样。关闭的是类似的东西(我手上没有确切的版本，只是从我的脑海中记住):

SELECT users.*, actions.*
 FROM users
 LEFT JOIN actions ON users.id = (
  SELECT user_id FROM actions
  WHERE users.id = actions.user_id
  LIMIT 1
 )
GROUP BY actions.user_id

我会从 users 得到所有结果，然后从 actions 得到一个结果，但是 actions 的结果不会是最新的，显然它可以随意分组，我尝试了 MAX(actions.id)，但我没有运气。

有人知道解决办法吗？现在我必须从 users 获取所有结果，并在我的 php 代码中对每个结果进行另一个查询，我觉得有一种优雅且更快的方法可以做到这一点。

Answer 1

使用此查询:

select 
  user_id,
  max(date) date
from actions
group by user_id

您获得每个用户的最新日期，然后您必须将 2 个表连接到它:

select 
  u.id, u.name, a.id, a.action, a.date, a.str
from users u 
inner join actions a on a.user_id = u.id 
inner join (
  select 
    user_id,
    max(date) date
  from actions
  group by user_id
) g on g.user_id = a.user_id and g.date = a.date

如果您想要按 ID 而不是按日期的最新结果:

select 
  u.id, u.name, a.id, a.action, a.date, a.str
from users u 
inner join actions a on a.user_id = u.id 
inner join (
  select 
    user_id,
    max(id) id
  from actions
  group by user_id
) g on g.user_id = a.user_id and g.id = a.id