問題描述

我的數(shù)據(jù)庫中有這張表:

I have this table on my database:

sentBy 和 sentTo 是對(duì) 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 發(fā)送消息，但用戶 1 尚未發(fā)送任何消息).

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).

第二個(gè)問題是:我怎樣才能獲得每個(gè)用戶的最后一條消息?我正在詢問是否獲得發(fā)送給用戶的最新消息.

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.

如何在一個(gè) SQL SELECT 語句中獲取該信息?或者我可能需要兩次選擇.

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

我正在嘗試做一些類似 WhatsApp 的事情.您有一個(gè)聊天屏幕，其中列出了我與之交談過的用戶列表(我的第一個(gè)問題)，以及與他們交談的最后一條消息(我的第二個(gè)問題).

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).

也許我可以創(chuàng)建另一個(gè)名為 Conversation 的表，將 sentBy 和 sentTo 移動(dòng)到該表，并將最后一條帶有發(fā)送日期的消息移動(dòng)到它，但我認(rèn)為這不是一個(gè)好的設(shè)計(jì).

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.

我的兩個(gè)問題的結(jié)果是:

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 的預(yù)期結(jié)果:

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 子句中的條件，以便為任何用戶獲得類似的結(jié)果.

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

但是，我的方法是創(chuàng)建一個(gè)視圖，然后從中進(jìn)行選擇，如下所示:

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;

我猜這對(duì)其他用途也很有用.

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

這篇關(guān)于選擇與我交談過的用戶并與他們最后一條消息，例如 whatsapp的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！