Блог

Я создаю сайт чата и для представления чатов аудитории у меня есть 3 выпадающих списка — Спорт (по умолчанию все виды спорта), День / месяц / год, Пользователи онлайн / Общее количество пользователей.

Теперь, если по умолчанию выбрано all sports, и я выбираю 1 месяц и общее количество пользователей, ожидаемый результат должен быть

Мой запрос

 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
  

Один человек предположил, что с помощью моего метода я фактически умножаю строку из-за двух объединений, поэтому мне нужно сначала объединить, а затем объединить.

Итак, в конце концов, я тоже попробовал этот запрос

 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;
  

Но это также умножает результаты.

Где я допускаю ошибку в обоих запросах? Заранее спасибо и хорошего дня впереди.

ОБРАЗЕЦ ДАННЫХ:

 Chatroom name   Total Users   Online users               

Basketball
Roomname27          32              5
Roomname11          15              3
Roomname32           8              1 

Football
Roomname5           63              12
Roomname18          44               7
Roomname4           15               2
  

вы можете попробовать другой синтаксис :

 select Sports, 
    Name, 
    coalesce(
        ( 
            select count(distinct C.ChatUserLogId) 
            from ChatUserLog as C
            where C.RoomId = R.RoomId 
              and C.LoggedInTime >= DATEADD(DAY,-30,GETDATE()) 
        ), 
    0) AS TotalUsers,
    coalesce(
        (
            select count(distinct D.LoggedInUserID)
            from LoggedInUser as D
            where D.RoomId = R.RoomId 
        ),
    0) AS UserOnline
from Room R 
  

с таким синтаксисом вы никогда не получите больше результатов, чем номер roomname.
но у вас также есть комната без аудитории