MS SQL聊天室查询错误 [英] MS SQL chatroom query error

问题描述

我使用asp.net编写了一个聊天室，C＃& MS SQL。我有两个下拉菜单用于选择体育，然后另一个用于根据用户在线/总用户排序聊天室。



它不应该重复两次运动，有在线用户。



提前致谢，祝你有个美好的一天。



我尝试了什么：



我用asp.net编写了一个聊天室，C＃& amp; MS SQL。我有两个下拉菜单用于选择体育，然后另一个用于根据用户在线/总用户排序聊天室。



< b>当我选择所有体育时（默认值）和总用户，查询工作正常

I have coded a chatroom using asp.net, C# & MS SQL. I have two Dropdowns for selecting Sports and then another for Sorting the chatrooms based on Users online/total users.

It should not repeat the sport twice, which has online users.

Thanks in advance and have a nice day ahead.

What I have tried:

I have coded a chatroom using asp.net, C# &amp; MS SQL. I have two Dropdowns for selecting Sports and then another for Sorting the chatrooms based on Users online/total users.

<b>When I select "All sports" (default value) and total users, the query works fine

推荐答案

我会尝试这样的事情：

I would try something like that:

SELECT roo.Sports As Sport, roo.[Name] AS ChatRoomName, COUNT(DISTINCT liu.[LoggedInUserID]) AS OnlineUsers, COUNT(DISTINCT chu.ChatUserLogId) AS TotalUsers
FROM Room AS roo
    LEFT JOIN LoggedInUser AS liu ON roo.RoomID = liu.RoomID
    LEFT JOIN ChatUserLog AS chu ON roo.RoomID = chu.RoomID AND chu.LoggedInTime >= DATEADD(DAY,-1,GETDATE()
GROUP BY roo.Sports, roo.[Name]

详情请见：

SQL联接的可视化表示 [ ^ ]

^ ]

COUNT（Transact-SQL） [ ^ ]

For further details, please see:
Visual Representation of SQL Joins[^]
Aggregate Functions (Transact-SQL)[^]
COUNT (Transact-SQL)[^]

这篇关于MS SQL聊天室查询错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！