MySQL加入2个表并检索最新的时间戳和消息

Question

我试图重新创建一个对话列表，如whatsapp，它显示了你正在与一个对话的人的联系人名称，也是消息发布的时间，还最后一条消息，无论它是来自您还是发布该消息的联系人，换句话说，我需要始终查看联系人姓名，上次发布消息的时间以及可能由我发送的最后一条消息收件人，我还需要从users表中恢复其他用户的图像。

users表

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 | 

我试图从数据库中查询，如果我的用户id = 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）我想提取使用摆脱，FIRST_NAME和用户表
2的接触的网址），并从接触

因此，换句话说，提取从邮件表中的最新消息和时间戳我需要显示的最后一条消息和时间戳发表任我或来自消息表的其他联系人以及用户表中的网址和用户名，但不是我的用户名和图像网址就是联系人。

下面是该查询我到目前为止

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 

Answer 1

喜欢的东西这应该工作。

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) 

P.S.这实际上并不告诉你发送最新消息的人（你或你的联系人），为每个结果行同时获取senderid和userid可能更有用，而不仅仅是联系人ID。

Answer 2

尝试类似：

select * from (
select m.senderId userid, 
     u.first_name, 
     u.url, 
     m.message, 
     m.timestamp   
from users u inner join messages m 
       on u.userid=m.userid 
order by timestamp desc 
) t 
limit 1;