問題描述
我的數(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)!