选择与我交谈过的用户，并与他们交流最后一条消息，例如whatsapp

Question

我的数据库中有此表：

sentBy和sentToFK到User表。

在此表上，我有用户之间的消息：

sentBy |  sentTo  |     dateSent     |     body
-------+----------+------------------+-----------------
  1    |    2     | 11/21/2010 10:00 | Hey!
-------+----------+------------------+-----------------
  2    |    1     | 11/21/2010 10:50 | Hi!
-------+----------+------------------+-----------------
  1    |    2     | 11/21/2010 10:51 | msg body 1
-------+----------+------------------+-----------------
  2    |    1     | 11/21/2010 11:05 | msg body 2
-------+----------+------------------+-----------------
  1    |    3     | 11/21/2010 11:51 | msg body 3
-------+----------+------------------+-----------------
  3    |    1     | 11/21/2010 12:05 | msg body 4
-------+----------+------------------+-----------------
  1    |    3     | 11/21/2010 12:16 | msg body 5
-------+----------+------------------+-----------------
  4    |    1     | 11/21/2010 12:25 | msg body 6
-------+----------+------------------+-----------------

我需要知道与用户1交谈过的 用户以及与用户1交谈过的用户
。在这种情况下，对于用户2、3和4（请注意，用户4已向用户1发送了一条消息，但用户1尚未发送任何消息）。

第二个问题是： 我该如何与每个用户获取最后一条消息？ 我要求将最新消息发送给用户。

例如，如果我要询问用户1，则用户2的最新消息为：味精正文2。而用户3的最新消息为味精正文5。

如何在一条SQL SELECT语句中获取该信息？ 也许我将需要两个选择。

我正在尝试做类似 WhatsApp的
事情。在您的聊天屏幕上，有一个与我交谈过的用户列表（我的第一个问题）和与他们最近的消息（我的第二个问题）的列表。

也许我可以创建一个名为另一个表Conversation，移动sentBy和sentTo该表，并最后消息发送给它的日期，但我认为这不可能是一个不错的设计。

我的两个问题的结果是：

sentBy |  sentTo  |     dateSent     |     body
-------+----------+------------------+-----------------
  2    |    1     | 11/21/2010 11:05 | msg body 2
-------+----------+------------------+-----------------
  1    |    3     | 11/21/2010 12:16 | msg body 5
-------+----------+------------------+-----------------
  4    |    1     | 11/21/2010 12:25 | msg body 6
-------+----------+------------------+-----------------

Answer 1

以下查询将为用户1提供预期的结果：

select m.* from messages m
join (
  select auser,withuser,max(datesent) datesent from (
    select sentby as auser,sentto as withuser,datesent from messages 
    union
    select sentto as auser,sentby as withuser,datesent from messages 
    ) as ud
  group by auser,withuser
  ) maxud 
  on (m.datesent=maxud.datesent and maxud.auser in (m.sentBy,m.sentTo))
where auser=1

不用说，您可以更改where子句中的条件，以使任何用户获得相似的结果。

但是，我的方法是创建一个视图，然后从中选择视图，如下所示：

create view conversation_stuff as
select m.sentBy,m.sentTo,m.dateSent,m.body,maxud.auser,maxud.withuser
from messages m
join (
  select auser,withuser,max(datesent) datesent from (
    select sentby as auser,sentto as withuser,datesent from messages 
    union
    select sentto as auser,sentby as withuser,datesent from messages 
    ) as ud
  group by auser,withuser
  ) maxud 
  on (m.datesent=maxud.datesent and maxud.auser in (m.sentBy,m.sentTo))

select sentBy,sentTo,dateSent,body from conversation_stuff where auser=1;

我猜这也可能对其他用途有用。

编辑 ：更改user为auser无处不在，为sqlserver停止抱怨 并 避免[]s …