像 facebook 消息传递一样的 sql 组(mssql sp) [英] sql group by acting like facebook messaging (mssql sp)

问题描述

我正在尝试对诸如对话之类的消息进行分组，user1 是 @user(已登录用户)UserName, UserFullName 始终是未登录的用户(对话对象)消息，日期将是最后一条消息的人

im trying to group messages like conversations, user1 is @user (logged in user) UserName, UserFullName is always non logged in user (who the conversation is with) Message, Date will be whoever has the last message

example 1: 
FromUser | ToUser | Message | Date
User2    | User1  | hi      | 01/01/2013 20:00
User1    | User2  | hi back | 01/01/2013 21:00

userfullname and username will be from touser (non logged)
message and date from fromuser (logged in @user, as last message in group)

example 2: 
FromUser | ToUser | Message | Date
User1    | User2  | hi      | 01/01/2013 20:00
User2    | User1  | hi back | 01/01/2013 21:00

userfullname and username will be from fromuser (non logged in)
message and date from fromuser (logged in @user as its the last message in group)

如果你们中的任何人使用过他们的消息系统，这将像 Facebook 对话一样显示.谢谢大家！:) 一想到 sql，我的脑袋就炸了

This will show just like facebook conversations, if any of you have used their messaging system. thanks all! :) fries my brain just thinking about sql

SELECT        
CM.FromUser, CM.ToUser, CM.Message, CM.Date, 
U.UserId, U.UserFullName, U.UserName, U.UserPhoto
FROM
ConversationMessages AS CM 
INNER JOIN
Users AS U ON U.UserName = CM.FromUser
WHERE
CM.ToUser = @user
ORDER BY 
CM.Date DESC 

推荐答案

答案和你的之前的问题.但是，现在必须考虑到 @user 可以是消息中的任一用户.

The answer is similar to your earlier question. However, now, it must take into account that the @user could be either user in the message.

在这种情况下，row_number() 没有直接帮助.

In this case, row_number() is not directly of help.

区别如下.现在有一个子查询可以将两个用户按规范"顺序排列.因此，它们之间的所有消息都具有相同的 User1 和 User2(基于字母顺序).

Here are the differences. There is now a subquery to put the two users in "canonical" order. So, all messages between them have the same User1 and User2 (based on alphabetical order).

partition by 子句使用这些列，因此所有消息都包含在 seqnum 计算中.Users 表现在可以直接获取有关当前用户的信息.

The partition by clause uses these columns, so all messages are included in the seqnum calculation. The Users table now fetches information about the current user directly.

select FromUser, ToUser, Message, [Date], UserId, UserFullName, UserName, UserPhoto
from (SELECT CM.FromUser, CM.ToUser, CM.Message, CM.[Date], U.UserId,
             U.UserFullName, U.UserName, U.UserPhoto,
             row_number() over (partition by CM.User1, CM.User2
                                order by CM.[Date] desc) as seqnum
      FROM (select CM.*,
                   (case when FromUser < ToUser then FromUser else ToUser end) as User1,
                   (case when FromUser < ToUser then ToUser else FromUser end) as User2
            from ConversationMessages CM
           ) CM CROSS JOIN
           (select *
            from Users U
            where @user = u.UserName
           ) U
      WHERE @user in (CM.ToUser, CM.FromUser)
     ) s
WHERE seqnum = 1
ORDER BY s.[Date] DESC ;

以上返回@user的用户信息.对于其他参与者:

The above returns the user information for @user. For the other participant:

select FromUser, ToUser, Message, [Date], UserId, UserFullName, UserName, UserPhoto
from (SELECT CM.FromUser, CM.ToUser, CM.Message, CM.[Date], U.UserId,
             U.UserFullName, U.UserName, U.UserPhoto,
             row_number() over (partition by CM.User1, CM.User2
                                order by CM.[Date] desc) as seqnum
      FROM (select CM.*,
                   (case when FromUser < ToUser then FromUser else ToUser end) as User1,
                   (case when FromUser < ToUser then ToUser else FromUser end) as User2
            from ConversationMessages CM
           ) CM JOIN
           Users U
           on U.UserName <> @user and
              U.UserName in (CM.FromUser, CM.ToUser)
      WHERE @user in (CM.ToUser, CM.FromUser)
     ) s
WHERE seqnum = 1
ORDER BY s.[Date] DESC ;

这篇关于像 facebook 消息传递一样的 sql 组(mssql sp)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！