从MySQL数据库表中获取最后一个对话行 [英] Get Last conversation row from MySQL database table

问题描述

我在MYSQL中有一个数据库，它有一个像这样的聊天表.

I have a database in MYSQL and it has chat table which looks like this.

我正在使用此查询来获取这些记录

I am using this query for fetching these records

SELECT * FROM (
    SELECT * FROM `user_chats`
    WHERE sender_id =2 OR receiver_id =2
    ORDER BY id DESC
) AS tbl
GROUP BY sender_id, receiver_id 

但是我的要求是只有5,4个ID的记录.基本上我的要求id提取2个用户之间的最后一次对话.在2点和2点之间3个用户对话中有2条记录，我们只希望它们中的最后一条，即id = 5，这里不需要id = 2.

But my requirement is only 5,4 ID's records. basically my requirement id fetching last conversation in between 2 users. Here in between 2 & 3 user conversation has 2 records and we want only last one of them i.e. id = 5, here don't need id = 2.

那么我们如何为该结果编写查询?

So how we can write a query for that result?

推荐答案

SELECT 
 * 
FROM 
 user_chats uc 
WHERE
 not exists ( 
   SELECT 
    1 
   FROM 
     user_chats uc2 
   WHERE 
     uc2.created > uc.created AND 
     (
       (uc.sender_id = uc2.sender_id AND uc.reciever_id = uc2.reciever_id) OR  
       (uc.sender_id = uc2.reciever_id AND uc.reciever_id = uc2.sender_id)
     )
  )

这篇关于从MySQL数据库表中获取最后一个对话行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！