未获得正确的发送和接收消息计数

tvz2xvvm 于 2021-07-27 发布在 Java

关注(0)|答案(2)|浏览(384)

SELECT DISTINCT
    U.UserId as 'Id', 
    U.FullName as 'Name',
    (SELECT COUNT(*) FROM [Conversation] 
     WHERE FromUserId = 'user1' AND ToUserId = U.UserId) 'SentCount',
    (SELECT COUNT(*) FROM [Conversation] 
     WHERE ToUserId = 'user1' AND FromUserId = U.UserId) 'ReceivedCount'
FROM 
    [Conversation] C 
INNER JOIN 
    [User] U ON U.UserId = C.FromUserId
WHERE 
    C.ToUserId = 'user1'

查询返回一个结果，但不包括某些行。 Conversation 表包含相同的 FromUserId （发送消息用户）和 ToUserId （接收消息用户）。
以下是表格：

当前结果-

预期结果：

带有虚拟数据的表-

CREATE TABLE [dbo].[User](
    [Id] [int] NULL,
    [UserId] [varchar](5) NULL,
    [Name] [varchar](5) NULL,
    [Email] [varchar](11) NULL
) ON [PRIMARY]

INSERT [dbo].[User] ([Id], [UserId], [Name], [Email]) VALUES (1, N'user1', N'user1', N'user1@a.com')
INSERT [dbo].[User] ([Id], [UserId], [Name], [Email]) VALUES (2, N'user2', N'user2', N'user2@a.com')
INSERT [dbo].[User] ([Id], [UserId], [Name], [Email]) VALUES (3, N'user3', N'user3', N'user3@a.com')
INSERT [dbo].[User] ([Id], [UserId], [Name], [Email]) VALUES (4, N'user4', N'user4', N'user4@a.com')
INSERT [dbo].[User] ([Id], [UserId], [Name], [Email]) VALUES (5, N'user5', N'user5', N'user5@a.com')

CREATE TABLE [dbo].[Conversation](
    [Id] [int] NULL,
    [conversationId] [varchar](14) NULL,
    [messageId] [varchar](4) NULL,
    [fromUserId] [varchar](5) NULL,
    [toUserId] [varchar](5) NULL
) ON [PRIMARY]

INSERT [dbo].[Conversation] ([Id], [conversationId], [messageId], [fromUserId], [toUserId]) VALUES (1, N'con-user1user2', N'mes1', N'user1', N'user2')
INSERT [dbo].[Conversation] ([Id], [conversationId], [messageId], [fromUserId], [toUserId]) VALUES (2, N'con-user1user2', N'mes2', N'user1', N'user2')
INSERT [dbo].[Conversation] ([Id], [conversationId], [messageId], [fromUserId], [toUserId]) VALUES (3, N'con-user2user1', N'mes3', N'user2', N'user1')
INSERT [dbo].[Conversation] ([Id], [conversationId], [messageId], [fromUserId], [toUserId]) VALUES (4, N'con-user1user3', N'mes4', N'user1', N'user3')
INSERT [dbo].[Conversation] ([Id], [conversationId], [messageId], [fromUserId], [toUserId]) VALUES (5, N'con-user4user1', N'mes5', N'user4', N'user1')

有人能帮忙把所有的记录都包括进去吗？
谢谢！

sql sql-server sql-server-2008

来源：https://stackoverflow.com/questions/63174287/ms-sql-server-2008-query-doesnt-get-proper-send-and-received-message-count

2条答案

按热度按时间

r8uurelv1#

你不需要一个 join 在外部查询中。这将更简单地写为：

SELECT U.UserId as Id, U.FullName as Name,
    (SELECT COUNT(*)
     FROM [Conversation] c
     WHERE c.FromUserId = 'user1' AND c.ToUserId = U.UserId
    ) as SentCount,
    (SELECT COUNT(*)
     FROM [Conversation] c 
     WHERE c.ToUserId = 'user1' AND c.FromUserId = U.UserId
    ) as ReceivedCount
FROM [User] U ;

笔记：
限定所有列引用。这对于相关从句尤其重要。
为表指定别名，这些别名是表名的缩写。
仅对字符串和日期常量使用单引号。不要使用列别名。
这是一把小提琴。

赞(0）回复(0）举报 2021-07-27

a2mppw5e2#

你不需要在你的基本选择会话表。类似的方法可以工作，但可以通过子查询进行优化：

Select U.UserId as 'Id', 
 U.name as 'Name',
 isnull(fromSummed.sentCount, 0) 'SentCount',
 isnull(ToSummed.ReceivedCount, 0) 'ReceivedCount'
FROM [User] U 
outer apply (select count(*) as sentCount from [Conversation] cFrom where cFrom.FromUserId = 'user1' and ToUserId = U.UserId ) fromSummed
outer apply (select count(*) as ReceivedCount from [Conversation] cTo where cTo.ToUserId = 'user1' and cTo.FromUserId = U.UserId) ToSummed
where isnull(fromSummed.sentCount, 0)>0 or isnull(ToSummed.ReceivedCount, 0)>0

赞(0）回复(0）举报 2021-07-27

我来回答

未获得正确的发送和接收消息计数

2条答案

相关问题

热门标签

最新问答