时间戳

首页 > 解决方案 > 两个用户之间交换的消息 SQL Server

sql - 两个用户之间交换的消息 SQL Server

问题描述

[在此处输入图片描述][1]我有一个用户之间的消息时间表。

我需要一个查询，该查询返回按名称在特定用户之间交换的消息列表。

![2]

现在需要的结果

用户名 邮件正文
穆罕默德118 xxxxxxxxxxx
巴拉霍 xxxxxxxxxxxxx
穆罕默德118 xxxxxxxxxxxxx

当前查询尝试：

select Users.UserName, Messages.MessageBody
from Users, Messages
where Users.UserId = Messages.MessageSender
   or Users.UserId = Messages.MessageRecipient
   and Users.UserId in (select Messages.MessageSender 
                        from Users, Messages
                        where Users.UserId = Messages.MessageSender
                          and Users.UserName = 'Mohammad118')
   and Users.UserId in (select Messages.MessageRecipient 
                        from Users, Messages
                        where Users.UserId = Messages.MessageRecipient
                          and Users.UserName = 'BaraaHo')

标签： sqlsql-server

解决方案

您可以为消息发送者应用过滤器并应用 JOIN 与用户获取用户名，如下所示：

;WITH cte_ExchangeUsers as
(select UserId FROM 
Users 
WHERE UserName IN ('Mohammad118', 'BaraaHo')
)
SELET u.UserName, m.MessageBody
FROM Messages as m
INNER JOIN Users as u
ON u.UserId = m.MessageSender
WHERE messageSender in -- Sender is one of the two persons
(
select UserId FROM 
cte_ExchangeUsers 
)
and messageReceipient in -- receiver is one of the two persons
(
Select UserId from cte_ExchangeUsers 
)

推荐阅读