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

2021-09-24 00:00:00 whatsapp sql sql-server

我的数据库中有这张表:

I have this table on my database:

sentBy 和 sentTo 是对 User 表的 FK.

sentBy and sentTo are FK to User table.

在这张桌子上我有用户之间的消息:

On this table I have messages between users:

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 尚未发送任何消息).

I need to know the users with whom user 1 has talked and users that have talked with user 1. In this case, with users 2, 3 and 4 (note that user 4 has sent a message to user 1, but user 1 hasn't sent any message yet).

第二个问题是:我怎样才能获得每个用户的最后一条消息?我正在询问是否获得发送给用户的最新消息.

And the second question is: how can I get the last message with each user? I'm asking about to get the latest message sent to a user.

例如，如果我询问用户 1，则用户 2 的最新消息是:msg body 2.用户 3 的最新消息是 msg body 5.

For example, if I'm asking about user 1, the latest message with user 2 is: msg body 2. And the latest message with user 3 is msg body 5.

如何在一个 SQL SELECT 语句中获取该信息?或者我可能需要两次选择.

How can I get that info in one SQL SELECT statement? Or maybe I will need two selects.

我正在尝试做一些类似 WhatsApp 的事情.您有一个聊天屏幕，其中列出了我与之交谈过的用户列表(我的第一个问题)，以及与他们交谈的最后一条消息(我的第二个问题).

I'm trying to do something like WhatsApp. Where you have a chats screen with a list of users with whom I have talked (my first question), and the last message with them (my second question).

也许我可以创建另一个名为 Conversation 的表，将 sentBy 和 sentTo 移动到该表，并将最后一条带有发送日期的消息移动到它，但我认为这不是一个好的设计.

Maybe I can create another table named Conversation, move sentBy and sentTo to that table, and also last message with the date sent to it, but I think this can't be a good design.

我的两个问题的结果是:

The result for my two question is this:

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
-------+----------+------------------+-----------------

推荐答案

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

The following query will give you the expected results for user 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 子句中的条件，以便为任何用户获得类似的结果.

Needless to say, you can change the condition in the where clause to get similar results for any user.

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

However, my approach would be to create a view and later select from it, like so:

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;

我猜这对其他用途也很有用.

This could prove useful for other uses too, I guess.

EDIT:在任何地方将 user 更改为 auser，以便 sqlserver 停止抱怨和以避免 []s...

EDIT: Changed user to auser everywhere, for sqlserver to stop complaining and to avoid []s...

相关文章