mysql - MySQL连接2个表并检索最新的时间戳和消息

我正在尝试重新创建一个类似于whatsapp的对话列表，其中显示您正在与之交谈的人的联系人姓名，以及消息的发布时间以及最后一条消息，无论它是来自您还是来自该联系人的发布消息，换句话说，我需要始终查看联系人姓名和消息的最后发布时间以及我或收件人可能发送的最后一条消息，我还需要带回其他用户的图像从用户表中。

用户表

userid |  first_name  |       url          |
 ---------------------------------------------
 101   |    name1     | www.image_url1.jpg |
 102   |    name2     | www.image_url2.jpg |
 103   |    name3     | www.image_url3.jpg |
 104   |    name4     | www.image_url4.jpg |

留言表

MessageId  |  userid  | senderid | message  | timestamp |
-----------------------------------------------------------
    1      |    101   |    102   | message1 |  1234567  |
    2      |    102   |    101   | message2 |  1234578  |
    3      |    101   |    102   | message3 |  1235679  |
    4      |    104   |    101   | message4 |  1256379  |

我要从数据库查询的内容（如果我的userid = 101是我或联系人的最后一条消息和时间戳，以及联系人userid，first_name和url）

  userid  |  first_name |        url         |  message  |  timestamp |
  ---------------------------------------------------------------------
    102   |    name2    | www.image_url2.jpg |  message3 |   1235679  |
    104   |    name4    | www.image_url4.jpg |  message4 |   1227878  |

1）我想从用户表中提取联系人的用户ID，名字和URL
2）从联系人的消息表中提取最新消息和时间戳

所以换句话说，我需要显示我或消息表中其他联系人发布的最后一条消息和时间戳，以及用户表中的URL和用户名，而不是联系人的我的用户名和图像URL。

这是我到目前为止的查询

SELECT DISTINCT users.userid, users.first_name, users.url, message, MAX(messages.timestamp) AS utime FROM messages JOIN users ON users.userid = messages.senderid WHERE users.userid <> '101' AND (messages.userid = '101' OR messages.senderid = '101') GROUP BY 1,2,3 ORDER BY utime DESC

我也尝试了下面的工作

SELECT DISTINCT mems.userid, mems.first_name, mems.url, message, MAX(messaging.timestamp) AS utime
FROM messaging
JOIN mems
ON mems.userid = CASE
                 WHEN
                      messaging.senderid = '101'
                   THEN
                      messaging.userid
                   ELSE
                      messaging.senderid
                 END
WHERE mems.userid <> '101' AND (messaging.userid = '101' OR messaging.senderid = '101')
GROUP BY 1,2,3 ORDER BY utime DESC

最佳答案

这样的事情应该起作用。

SELECT IF(m.userid = '101', m.senderid, m.userid) contact, m.message, m.timestamp, u.first_name, u.url
FROM messages m
INNER JOIN(
    SELECT MAX(timestamp) max_time, IF(userid = '101', senderid, userid) contact
    FROM messages
    WHERE userid = '101' OR senderid = '101'
    GROUP BY contact
) j ON j.contact = IF(m.userid = '101', m.senderid, m.userid) AND m.timestamp = j.max_time
LEFT JOIN users u ON u.userid = IF(m.userid = '101', m.senderid, m.userid)

附言这实际上并不能告诉您谁发送了最新消息（您或您的联系人），获取每个结果行的senderid和userid而不是仅获取联系人ID可能更有用。