SQL分组：用户之间的最新记录

Maybe I'm having a really bad day, but could someone possibly help me to turn this:

也许我有一个非常糟糕的一天,但有人可能会帮助我转变:

MessageID | SendingUserID | ReceivingUserID
-------------------------------------------
1073      | 1002          | 1001
1065      | 1001          | 1002
1076      | 1008          | 1002

Into:

MessageID | SendingUserID | ReceivingUserID
-------------------------------------------
1073      | 1002          | 1001
1076      | 1008          | 1002

Whereby,only the most recent message between two users is listed?

因此,仅列出两个用户之间的最新消息?

3 个解决方案

#1

try this:

SELECT Message.*
FROM Message
WHERE Message.MessageID IN
(SELECT MAX(MessageID) FROM Message 
    GROUP BY 
        CASE WHEN ReceivingUserID > SendingUserID 
            THEN ReceivingUserID ELSE SendingUserID END,
        CASE WHEN ReceivingUserID > SendingUserID
            THEN SendingUserID ELSE ReceivingUserID END
)

#2

The exclusive self join approach:

独家自我加入方式:

select *
from YourTable a
left join YourTable b 
    on (
        (a.SendingUserID = b.SendingUserID
        and a.ReceivinggUserID = b.ReceivingUserID)
        or (a.SendingUserID = b.ReceivingUserID
        and a.ReceivinggUserID = b.SendingUserID)
    ) and b.messageid > a.messageid
 where b.messageid is null

The join on "b" searches for later messages between the same users. The WHERE clause filters for messages that do not have a later message. This gives you only the latest message between each pair of users.

“b”上的联接搜索相同用户之间的后续消息。 WHERE子句筛选没有后续消息的消息。这为您提供了每对用户之间的最新消息。

#3

You could use a CTE (Common Table Expression) in SQL Server 2005 and higher to make sure the two UserID's are always the smaller one before the larger one, and then just get the maximum for each of those combinations:

您可以在SQL Server 2005及更高版本中使用CTE(公用表表达式)来确保两个用户ID在较大的用户ID之前总是较小,然后只获得每个组合的最大值:

WITH Messages(MessageID, User1, User2)
AS 
(
    SELECT
        MessageID,
        CASE 
          WHEN SendingUserID < ReceivingUserID
          THEN SendingUserID
          ELSE ReceivingUserID 
        END as 'User1',
        CASE 
          WHEN SendingUserID < ReceivingUserID
          THEN ReceivingUserID 
          ELSE SendingUserID
        END as 'User2'
    FROM 
        MyMessages
)
SELECT 
    MessageID, User1, User2 
FROM 
    Messages m1
WHERE
    MessageID = (SELECT MAX(MessageID) FROM  Messages m2 
                 WHERE m1.User1 = m2.User1 AND m1.User2 = m2.User2)

The inner SELECT inside the CTE would "order" your messages like this:

CTE中的内部SELECT会像这样“命令”你的消息:

MessageID   User1   User2
  1065       1001    1002
  1073       1001    1002
  1076       1002    1008

and the outer SELECT based on this CTE then just simply picks the entry with the maximum MessageID for every combination of (User1, User2).

然后,基于此CTE的外部SELECT只是为(User1,User2)的每个组合选择具有最大MessageID的条目。

Marc

#1