MySql 中的复杂一对多查询

Question

我已经搜索过，但无法将问题浓缩到足以找到相关答案。我的经验告诉我这可能是一个设计问题...

current_status
id
user_id
status_id
created

users
user_id
other...

current_status 主要用于审计目的。每次用户状态发生变化时，都会在 current_status 中插入一条新记录。一些状态示例包括“工作”、“解雇”、“已申请”、“裁员”等。

查询 - 我想返回每个用户的最新状态以及用户详细信息...

SELECT users.*, current_status.status_id AS status 
FROM users 
JOIN current_status ON users.user_id = current_status.user_id 
WHERE users.user_id = '1'

我如何告诉查询返回用户最新的status_id？

ORDER BY current_status.created DESC LIMIT 0,1

然而，这真的是准确的方法吗？因为如果我想返回多个用户怎么办？

任何帮助将不胜感激。

Answer 1

编辑 我想我现在已经掌握了您的表/列...

SELECT    users.*,
         (SELECT   current_status.status_id
          FROM     current_status
          WHERE    current_status.user_id = users.user_id
          ORDER BY current_status.created DESC
          LIMIT    1) AS status
FROM      users
WHERE     users.user_id = '1'

EDITv2 此外，如果您想加入该表以节省以后的查询，你可以使用：

SELECT    users.*, current_status.*
FROM      users
  LEFT JOIN current_status
  ON        current_status.status_id = (
    SELECT   current_status.status_id
    FROM     current_status
    WHERE    current_status.user_id = users.user_id
    ORDER BY current_status.created DESC
    LIMIT    1
  )
WHERE     users.user_id = '1'

Answer 2

也许是这样的？

SELECT users.*, current_status.status_id AS status
FROM
users,
current_status
WHERE users.user_id = '1' AND
users.user_id = current_status.user_id AND
current_status.status_id IN (SELECT max(status_id) FROM current_status GROUP_BY user_id))

Answer 3

遗憾的是 MySQL 不支持 over(order by ...) 功能。这将需要一个子查询来选择所有最后的状态：

select 
  user.user_id, status.status_id, status.created
from user 
  join status on user.user_id = status.user_id
  join (
  select user_id, max(created)
  from status
  group by user_id
) last_status
on 
  status.user_id = last_statuses.user_id
    and status.created = last_statuses.created