mysql - 左连接以获取最新条目

Question

我有表 users 和 statuses 。我想选择所有用户以及他们可能具有的任何状态，但仅选择每个用户的最新状态。

这是不起作用的代码:

SELECT users.id, alias, gender, login, logout, users.create_date, statustext as statustxt, TIMESTAMPDIFF(YEAR,birthdate,CURDATE()) AS age
                FROM users
                LEFT JOIN statuses s ON users.id = s.user_id
                WHERE s.ID = (                              
                        SELECT MAX(s2.ID)                    
                        FROM statuses s2
                        WHERE s2.user_id = s.user_id         
                        )

这会获取具有最新状态的用户，但也不会获取用户表中的用户。也许可以通过一些小的调整来解决？

我通过搜索得到了子查询，但我不明白该代码是如何工作的。它似乎比较同一个表的两个版本(例如: WHERE s2.user_id = s.user_id )。我在哪里可以阅读有关此类技术的信息？顺便问一下，在这种情况下需要子查询吗？

如果您能找到解决方案，那就太好了，并且非常感谢对其工作原理的一些基本解释。

----------编辑----------------

我采取了其中一个响应(由 maresa 提供)并与我的初始代码的子查询相结合，这有效(!)它有 3 个选择，看起来有点过于复杂？:

 SELECT users.id, alias, gender, login, logout, users.create_date, statustext as statustxt, TIMESTAMPDIFF(YEAR,birthdate,CURDATE()) AS age
FROM users
    LEFT JOIN (
        SELECT user_id, statustext FROM statuses s
         WHERE s.ID = (                             
           SELECT MAX(s2.ID)                
           FROM statuses s2
           WHERE s2.user_id = s.user_id
     )
  ) as s ON users.id = s.user_id

Answer 1

我也遇到过类似的问题。这篇文章相关:http://www.microshell.com/database/sql/optimizing-sql-that-selects-the-maxminetc-from-a-group/ .

关于您的具体查询，由于您只关心最新状态，因此您希望首先从每个用户那里获取最新状态。假设最新状态具有最新 id(基于您的示例)，SQL 将如下:

SELECT
    MAX(ID), statustext, user_id
FROM
    statuses
GROUP BY
    user_id

上面的查询的作用是获取每个 user_id 的最新状态。一旦你明白了，你就可以把它想象成一张 table 。然后只需加入这个“表”(查询)而不是真正的表(状态表)。因此，您的查询如下所示:

SELECT
    users.id, alias, gender, login, logout, users.create_date, statustext as statustxt, TIMESTAMPDIFF(YEAR,birthdate,CURDATE()) AS age
FROM
    users
    LEFT JOIN (
        SELECT
            MAX(ID), user_id
        FROM
            statuses
        GROUP BY
            user_id
    ) as s ON users.id = s.user_id
    LEFT JOIN statuses ON statuses.ID = s.ID -- EDIT: Added this line.