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

Question

我正在尝试重新创建一个对话列表，比如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)我想从users表中提取联系人的userid、first_name和url

2)从联系人的消息表中提取最新消息和时间戳。

因此，换句话说，我需要显示我或其他联系人从messages表发布的最后一条消息和时间戳，以及来自users表的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

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)

实际上，这并不能告诉您是谁发送了最新的消息(您或您的联系人)--对于每个结果行，获取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;