SQL查询/获取基于最新的入门两种不同的条件

Question

这是我的表结构：

[id] [senderid] [recipientid] [message] [datetime] 

我想每个对话最新的条目（基于日期时间）（对某些用户），结果应该是（为自己的用户ID 1）：

435 | 1 | 67 | how are u? | timestampnow 

下一个结果（响应后）：

436 | 67 | 1 | fine thanks | timestamplater 

困惑怎么办q uery /加入正确。我试着像somethink：

SELECT * FROM messages MSG 

INNER JOIN 
(SELECT MAX(id) MAXDATE,recipientid,senderid 
FROM messages 
GROUP BY recipientid,senderid) MSG2 

ON MSG.recipientid = MSG2.recipientid 
AND MSG.id = MAXDATE 

Answer 1

select m.* 
from (
    select max(id) as id from (
     select max(id) as id, recipientid as user_id 
     from messages 
     where senderid = ? 
     group by recipientid 
     union all 
     select max(id) as id, senderid as user_id 
     from messages 
     where recipientid = ? 
     group by senderid 
    ) sub 
    group by user_id 
) sub 
join messages m using(id) 

最里面的子查询将返回达到每谈话2 id秒（id从去年发送的消息和id从最后收到的消息）。外部子查询将抓取两个最高的id s。然后将结果与messages表连接以返回相应的行。

（更换?与给定用户ID）

一个较短的方式可以是：

select m.* 
from (
    select max(id) as id 
    from messages 
    where ? in (senderid, recipientid) 
    group by case when senderid = ? then recipientid else senderid end 
) sub 
join messages m using(id) 

但是，一个可能变慢。

Answer 2

Firts建立的条件查询：

SELECT recipientid, MAX(date) mdate 
FROM yourTable 
GROUP BY recipientid 

使用它作为一个子查询：

SELECT a.[id], a.[senderid], a.[recipientid], a.[message], a.[datetime] 
FROM yourTable a 
    INNER JOIN (SELECT recipientid, MAX(date) mdate 
       FROM yourTable 
       GROUP BY recipientid) b ON b.recipientid = a.recipientid 
             AND a.datetime = b.mdate; 

Answer 3

你不需要子选择有

它可能就这么简单：

SELECT id, senderid, recipientid, message, MAX(datetime) as datetime 
FROM yourTable 
GROUP BY recipientid;