Блог

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

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

 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 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
  

И таблица и их столбцы выглядят следующим образом

 CREATE TABLE [dbo].[Room] (
            [RoomID]        INT           IDENTITY (6666, 1) NOT NULL,
            [Name]          VARCHAR (100) NOT NULL,
            [Sports]        VARCHAR (50)  NOT NULL,
            [CreatedDate]   DATETIME      DEFAULT (getdate()) NOT NULL,
            [CreatedBy]     VARCHAR (50)  NOT NULL,
            [CreatedUserID] INT           NOT NULL,
            PRIMARY KEY CLUSTERED ([RoomID] ASC)
        );


CREATE TABLE [dbo].[ChatUserLog] (
    [ChatUserLogId] BIGINT   IDENTITY (1, 1) NOT NULL,
    [MId]           INT      NOT NULL,
    [RoomId]        INT      NOT NULL,
    [LoggedInTime]  DATETIME DEFAULT (getdate()) NOT NULL,
    [LogOutTime]    DATETIME DEFAULT (getdate()) NULL,
    PRIMARY KEY CLUSTERED ([ChatUserLogId] ASC)
);



CREATE TABLE [dbo].[LoggedInUser] (
    [LoggedInUserID] INT           IDENTITY (1, 1) NOT NULL,
    [MId]            INT           NOT NULL,
    [RoomID]         INT           NOT NULL,
    [NickName]       VARCHAR (50)  NOT NULL,
    [Icon]           VARCHAR (MAX) NOT NULL,
    CONSTRAINT [PK_LoggedInUser] PRIMARY KEY CLUSTERED ([LoggedInUserID] ASC)
);
  

Для этого приведенного выше запроса, скажем, если у меня есть 7 тем для чата по баскетболу, я получаю отображение любых 3 комнат баскетбола, и весь баскетбол повторяется 7 раз (также не отсортирован должным образом, как я ожидал), и аналогично, если у меня есть 15 тем для чата по футболу, я получаю повторение всего футбола 15 раз без надлежащей сортировки.

Какие ошибки я здесь допустил? Заранее спасибо и хорошего дня впереди.

Что ж, с вашим методом вы фактически умножаете строку из-за двух объединений.

Итак, сначала вам нужно выполнить агрегацию, а затем присоединиться.

Итак, в конце :

 with agg_ChatUserLog as (
 select RoomId, count(*) as cnt_user_tot
 from ChatUserLog
 group by RoomId),
agg_LoggedInUser as (
 select RoomId, count(*) as cnt_user_logged
 from LoggedInUser
 group by RoomId)
select 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