2左连接（MSSQL）放在一起乘以结果。但它不应该 [英] 2 left joins (MSSQL) put together multiplying the results. But it shouldn't

问题描述

我正在建立聊天网站并向观众展示聊天记录，我有3个下拉列表 - 体育（默认为所有体育项目），日/月/年，在线用户/总用户数。



现在，如果默认情况下选择所有体育项目并选择1个月和总用户数，则预期结果应为



我的查询是

  SELECT   DISTINCT  roo。[运动]，
 roo。[名称]，
 COUNT（ DISTINCT  chu.ChatUserLogId） AS  TotalUsers，
 COUNT（ DISTINCT  liu。[LoggedInUserID]） AS  UserOnline 
 
  FROM 房间 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，-30，GETDATE（））
 
  GROUP   BY  roo。[Sports]，roo。[Name] 
  ORDER   BY  TotalUsers  DESC  

一个人建议用我的方法我实际上是因为两个连接和&所以我需要首先聚合，然后加入。



所以最后，我也尝试了这个查询

与agg_ChatUserLog为（选择RoomId，count（*）为cnt_user_tot，来自ChatUserLog WHERE LoggedInTime> = DATEADD（DAY，-30，GETDATE（））group by RoomId），
 
 agg_LoggedInUser as（从RoomId选择RoomId，计数（*）为来自LoggedInUser组的cnt_user_logged）
 
从房间选择Sports，Name，cnt_user_tot，cnt_user_logged r 
 
左外连接agg_ChatUserLog acu on acu.RoomId = r.RoomId 
 
 left al join agg_LoggedInUser alu on alu.RoomId = r.RoomId; 

但这也是结果的倍增。



我在哪里查询错误？



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



我尝试过的：



我想，问题在于GROUP BY项目......所以，我正在尝试修改并试试它们<使用agg_ChatUserLog作为（b选择RoomId，计数（*）作为来自ChatUserLog WHERE LoggedInTime& gt;中的cnt_user_tot = DATEADD（DAY，-30， GETDATE（））by RoomId），agg_LoggedInUser as（选择RoomId，计数（*）作为来自RoomId的LoggedInUser组的cnt_user_logged）从房间r左侧外部连接agg_ChatUserLog acu上选择Sports，Name，cnt_user_tot，cnt_user_logged on acu.RoomId = r。 RoomId左外连接agg_LoggedInUser alu on alu.RoomId = r.RoomId GROUP BY cnt_user_tot ORDER BY cnt_user_tot DESC

解决方案

这可能是你的GROUP BY子句 - 它分别根据两个列创建组，而不是组合。

所以它做的是疯狂的一组基于第一列，然后根据第二列将它们分开。

这可能有助于解释它的作用： [ ^ ]

我无法直接建议解决方案，因为我不确定您输入的内容和期望在没有输入和输出数据的实际示例的情况下接收。

I am building a chat site and for presenting the chats to the audiences, I have 3 Dropdownlists - Sports (Default is All Sports), Day/Month/Year, Users online/Total Users.

Now, if by default all sports is selected and I pick 1 month and Total users, the expected result should be

My query is

SELECT DISTINCT roo.[Sports], 
                roo.[Name], 
                COUNT(DISTINCT chu.ChatUserLogId) AS TotalUsers, 
                COUNT(DISTINCT liu.[LoggedInUserID]) AS UserOnline

 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,-30,GETDATE()) 

 GROUP BY roo.[Sports], roo.[Name]   
 ORDER BY TotalUsers DESC

One person suggested that with my method I am actually multiplying the row because of the two joins & so I need to aggregate first, then join.

So in the end, I tried this query too

with agg_ChatUserLog as (select RoomId, count(*) as cnt_user_tot from ChatUserLog WHERE LoggedInTime >= DATEADD(DAY,-30,GETDATE()) group by RoomId),                                            

       agg_LoggedInUser as (select RoomId, count(*) as cnt_user_logged from LoggedInUser group by RoomId)

select Sports, Name, cnt_user_tot, cnt_user_logged from Room r 

       left outer join agg_ChatUserLog acu on acu.RoomId = r.RoomId 

       left outer join agg_LoggedInUser alu on alu.RoomId = r.RoomId;

But this is also multiplying the results.

Where am I making the mistake in both the query?

Thanks in advance and have a nice day ahead.

What I have tried:

I guess, the issue is with "GROUP BY" items too... So, I am trying to modify and try them out too in

with agg_ChatUserLog as (select RoomId, count(*) as cnt_user_tot from ChatUserLog WHERE LoggedInTime >= DATEADD(DAY,-30,GETDATE()) group by RoomId), agg_LoggedInUser as (select RoomId, count(*) as cnt_user_logged from LoggedInUser group by RoomId) select Sports, Name, cnt_user_tot, cnt_user_logged from Room r left outer join agg_ChatUserLog acu on acu.RoomId = r.RoomId left outer join agg_LoggedInUser alu on alu.RoomId = r.RoomId GROUP BY cnt_user_tot ORDER BY cnt_user_tot DESC

解决方案

It's probably your GROUP BY clause - it creates groups based on both of the columns separately, not as a combination.
So what it does is crazy a set of groups based on the first column, and then separate them based on the second column.
This may help to explain what it does: SQL GROUP By and the "Column 'name' is invalid in the select list because..." error[^]
I can't suggest a solution directly because I'm not sure exactly what you are inputting and expecting to receive without actual examples of input and output data.

这篇关于2左连接（MSSQL）放在一起乘以结果。但它不应该的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！