mysql - 同一列上有 2 个 INNER JOIN 的 SQL 不起作用

Question

我正在尝试使用以下代码来获取当前用户最近与之聊天的 6 个用户。我有两个问题。首先，如果当前用户已收到来自其他用户的消息但仅发送了消息，则不会获取该其他用户。其次，ORDER BY 子句导致了错误。我是 SQL 初学者，所以我不知道发生了什么。提前致谢!

代码如下:

SELECT users.* 
FROM users INNER JOIN
    messages fromuser 
    ON (fromuser.fromid = users.id) INNER JOIN
    messages touser 
    ON (touser.toid = users.id)
WHERE fromuser.toid = :userid OR touser.fromid = :meid
GROUP BY users.id
ORDER BY MAX(messages.datetime)
LIMIT 6;

Answer 1

这应该可以完成你的工作，并且它比你到目前为止的其他答案更少依赖 MySQL 扩展。我估计它的性能大致相同，但肯定更冗长。

SELECT u.* 
FROM (
  SELECT DISTINCT otherid
  FROM (
    SELECT
       m.fromid AS otherid,
       MAX(m.datetime) as maxts
    FROM messages m
    WHERE m.toid = :userid
    GROUP BY m.fromid

    UNION ALL

    SELECT
      m.toid AS otherid,
      MAX(m.datetime) as maxts
    FROM messages m
    WHERE m.fromid = :userid
    GROUP BY m.toid
  ) um
  ORDER BY maxts DESC
  LIMIT 6
) otheru
INNER JOIN users u
  ON u.id = otheru.otherid