合并多行为一行具有相同的值交换之间的两列在SQL Server

Question

我正在开发一个基于api的简单聊天模块。我试图得到一个特定的用户的聊天对话，但由于2列具有相同的值彼此交换导致我的数据被复制。

我想合并行具有相同的值交换之间的2列和合并的行应该基于最新的条目插入到数据库。

数据如下:

Id  To  From   Message        ConversationTime
1   1    2     hello              11:00AM
2   3    1     hi                 12:00PM
3   1    3     how are you?       12:15PM
4   3    1     I am fine.         12:30PM
5   4    5     Hi!                04:30PM
6   5    4     Hello              04:35PM
7   1    5     Hola!              06:30PM

因此，例如，如果用户Id为1我的结果需要看起来像这样:

Id  To  From   Message        ConversationTime
1   1    2     hello              11:00AM
4   3    1     I am fine.         12:30PM
7   1    5     Hola!              06:30PM

如果Id是5，那么结果将是这样的:

Id  To  From   Message        ConversationTime
6   5    4     Hello              04:35PM
7   1    5     Hola!              06:30PM

我的结果集看起来像这样:

Id  To  From   Message        ConversationTime
1   1    2     hello              11:00AM
3   1    3     how are you?       12:15PM
4   3    1     I am fine.         12:30PM
7   1    5     Hola!              06:30PM

任何帮助都会很感激。提前感谢!

Answer 1

的想法与链接的副本相同。只需使用CASE表达式获取其他用户的ID:

DECLARE @ID int = 1;
WITH RNs AS(
SELECT ID,
[To], --TO is a reserved keyword and should not be used for object names
[From], --FROM is a reserved keyword and should not be used for object names
Message,
ConversationTime, --I assume this is a time
ROW_NUMBER() OVER (PARTITION BY CASE [To] WHEN @ID THEN [From] ELSE [To] END ORDER BY ConversationTime DESC) AS RN --TO and FROM are reserved keywords and should not be used for object names
FROM dbo.YourTable
WHERE @ID IN ([To],[From])) --TO and FROM are reserved keywords and should not be used for object names
SELECT ID,
[To], --TO is a reserved keyword and should not be used for object names
[From], --FROM is a reserved keyword and should not be used for object names
Message,
ConversationTime --I assume this is a time
FROM RN
WHERE RN = 1;

Answer 2

SQL Server允许您在没有case表达式的情况下通过取消数据的旋转，然后使用窗口函数来完成此操作:

select t.*
from (select t.*,
row_number() over (partition by v.user_other order by t.conversationTime desc) as seqnum
from t cross apply
(values (t.to, t.from), (t.from, to.to)
) v(user, user_other)
where v.user = 1
) t
where seqnum = 1;