通过匹配 SQL Server 中的两列对结果进行分组

本文介绍了通过匹配 SQL Server 中的两列对结果进行分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我使用的是 SQL Server 2008 R2.我有一个名为 Messages 的表，我在其中存储每个用户发送给其他用户的用户消息.表结构如下.

I am using SQL Server 2008 R2. I have a table called Messages where I store user messages each user send to other users. The table structure is like below.

+--------+----------+-----------------+------------+ | Sender | Receiver | Message | Date | +--------+----------+-----------------+------------+ | John | Dennis | How are you | 2015-06-06 | | John | Dennis | Hi | 2015-06-05 | | Tom | John | How much is it? | 2015-06-04 | | Tom | John | Did you buy it? | 2015-06-03 | | Robin | Tom | Hey man | 2015-06-03 | | Dennis | John | What up | 2015-06-02 | | John | Tom | Call me | 2015-06-01 | +--------+----------+-----------------+------------+

我想为每个对话获取选定用户的最新消息和其他参与者的姓名.例如，有三个对话.一个在john-Dennis"之间，第二个是John-Tom"，第三个在Robin-Tom"之间.

I want to get the newest message and other participants' name for a selected user for each conversation. For a example there are three conversations. One is between "john-Dennis" and 2nd one is "John-Tom"and 3rd one between "Robin-Tom".

如果我想获取用户 john 的对话，我想获取包含对话中其他用户姓名的最新对话消息.

If I want to get conversations for user john, I want to get the latest conversation message with the name of other user who is in the conversation.

上述场景的预期结果应该是这样的.

The expected result for the above scenario should be like this.

+-------------+-----------------+------------+ | Participant | Message | Date | +-------------+-----------------+------------+ | Dennis | How are you | 2015-06-06 | | Tom | How much is it? | 2015-06-04 | +-------------+-----------------+------------+

如何在 SQL Server 中使用 SQL 查询来实现这一点.我挣扎了好几天.请帮忙.提前致谢.

How to achieve this using a SQL query in SQL Server. I am struggling with part for days. Please help. Thanks in advance.

推荐答案

可以稍微压缩一下，但我已将其拆分为简单的步骤，希望能让它更容易理解.

It's possible to compress this a bit, but I've split it into simple steps to hopefully make it a little easier to follow.

-- Sample data from the question. declare @msg table (Sender varchar(32), Receiver varchar(32), [Message] varchar(max), [Date] date); insert @msg (Sender, Receiver, [Message], [Date]) values ('John','Dennis', 'How are you', '2015-06-06'), ('Dennis', 'John', 'Hi', '2015-06-05'), ('Tom', 'John', 'How much is it?', '2015-06-04'), ('Tom', 'John', 'Did you buy it?', '2015-06-03'), ('Robin', 'Tom', 'Hey man', '2015-06-03'), ('Dennis', 'John', 'What up', '2015-06-02'), ('John', 'Tom', 'Call me', '2015-06-01'); -- The name of the user whose conversations you want to find. declare @UserName varchar(32) = 'John'; -- Step 1: Create columns [Participant1] and [Participant2] that will be the same for -- each pair of users regardless of who's the sender and who the receiver. with NameOrderCTE as ( select Participant1 = case when Sender < Receiver then Sender else Receiver end, Participant2 = case when Sender < Receiver then Receiver else Sender end, * from @msg ), -- Step 2: For each distinct pair of participants, create a [Sequence] number that -- puts the messages in reverse chronological order. MessageSequenceCTE as ( select *, [Sequence] = row_number() over (partition by Participant1, Participant2 order by [Date] desc) from NameOrderCTE ) -- Step 3: Get the most recent ([Sequence] = 1) messages for each conversation -- involving the target user. select Participant = case @UserName when Sender then Receiver else Sender end, [Message], [Date] from MessageSequenceCTE where @UserName in (Sender, Receiver) and [Sequence] = 1;