我的数据库上有这个表：
sentBy和sentTo是FK到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的最新消息是：msg body 2.而用户3的最新消息是msg body 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 -------+----------+------------------+-----------------I have this table on my database:
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 -------+----------+------------------+-----------------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.
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.
How can I get that info in one SQL SELECT statement? Or maybe I will need two selects.
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).
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的预期结果：
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 ...
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=1Needless 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: Changed user to auser everywhere, for sqlserver to stop complaining and to avoid []s...
更多推荐
发布评论