mysql - 无法在我的查询中集成 INNER JOIN

Question

您好，我正在使用下面的查询从每个独特的对话中选择最新消息。一切正常，除非我尝试集成 INNER JOIN 以尝试将用户的 user_id 与其 users< 中的 username 链接起来表。

我已经尝试了几乎所有的组合来集成 INNER JOIN，所以我决定在获得帮助之前将它与工作查询分开。

我做了一个 fiddle http://sqlfiddle.com/#!9/a9bbc/1我只是想让右边的user_id打印用户的用户名

SELECT message_id, 
       msg, 
       user_id 
FROM   messages 
   JOIN (SELECT user_id, 
                Max(dat) m 
         FROM   ((SELECT message_id, 
                         recipient_id user_id, 
                         dat 
                  FROM   messages 
                  WHERE  owner_id = 1) 
                 UNION 
                 (SELECT message_id, 
                         owner_id user_id, 
                         dat 
                  FROM   messages 
                  WHERE  recipient_id = 1)) t1 
         GROUP  BY user_id) t2 
     ON ( ( owner_id = 1 
            AND recipient_id = user_id ) 
           OR ( owner_id = user_id 
                AND recipient_id = 1 ) ) 
        AND ( dat = m ) 
 ORDER  BY dat DESC

.

users.username INNER JOIN users ON messages.user_id = users.user_id;

Answer 1

如果 messages.message_id 是一个 AUTO_INCREMENT PRIMARY KEY 列，这可能是获得所需结果的最快方法:

select m.message_id, m.msg, u.username,
  case sub.other_user_id
    when m.owner_id     then 'received from'
    when m.recipient_id then 'sent to'
  end as direction
from (
  select other_user_id, max(message_id) as message_id
  from (
    select recipient_id as other_user_id, max(message_id) as message_id
    from messages
    where owner_id = @uid
    group by recipient_id

    union all

    select owner_id as other_user_id, max(message_id) as message_id
    from messages
    where recipient_id = @uid
    group by owner_id
  ) sub
  group by other_user_id
) sub
join messages m on m.message_id = sub.message_id
join users u    on u.user_id    = sub.other_user_id
order by sub.message_id desc

我还添加了 direction 列。这样您就可以知道消息是否已发送或收到。结果会是这样的:

| message_id |                                                              msg | username |     direction |
|------------|------------------------------------------------------------------|----------|---------------|
|          9 | You should also see this                                         |    User3 |       sent to |
|          8 | you should Now see this instead even with the owner_id flipped.. |    User2 | received from |

http://sqlfiddle.com/#!9/a9bbc/34

请注意，您仍然可以在 SELECT 子句中添加 users 和 messages 表中的任何列。

要使此查询快速运行，您需要在 messages(owner_id, recipient_id) 和 messages(recipient_id, owner_id) 上建立索引。