Блог

нужна ваша помощь с запросом.

У меня есть таблица Managers (ManagerId, ManagerName)

У меня есть таблица Statuses (StatusId, StatusName)
(В этой таблице около 10 статусов)

У меня есть таблица Clients (ClientId, ClientName, ManagerId, StatusId, WhenAdded)
( WhenAdded является типом datetime)

Очевидно, что field 'ManagerId' ссылается на таблицу 'Managers' , а field 'StatusId' — на таблицу 'Statuses' .

Пользователь хочет получить некоторую статистику о менеджерах за определенный период времени ( from startDate to endDate using field 'WhenAdded' ) в следующей таблице.

Столбцы:

ManagerName, NumberOfClients, NumberOfClientsWithStatus1, NumberOfClientsWithStatus2, NumberOfClientsWithStatus3 и так далее.

Количество столбцов с именем, NumberOfClientsWithStatusI где i — количество статусов, равное количеству строк в таблице 'Statuses' .

Как я могу это сделать?

t-sql, sql server 2008 r2 express edition.

 SELECT
    ManagerName,
    COUNT(*) AS NumberOfClients,
    COUNT(CASE WHEN S.StatusId = 1 THEN 1 ELSE NULL END) AS NumberOfClientsWithStatus1,
    COUNT(CASE WHEN S.StatusId = 2 THEN 1 ELSE NULL END) AS NumberOfClientsWithStatus2,
    COUNT(CASE WHEN S.StatusId = 3 THEN 1 ELSE NULL END) AS NumberOfClientsWithStatus3,
    ...
FROM
   Clients C
   JOIN
   Managers M ON C.ManagerId = M.ManagerId
   JOIN
   Statuses S ON C.StatusId = S.StatusId
WHERE
    M.WhenAdded BETWEEN @startDate AND @endDate
GROUP BY
    M.ManagerName
  

Примечание: нет чистого способа добавить произвольные номера столбцов состояния в SQL (не только SQL Server), потому что это фиксированный вывод столбца. Вам пришлось бы изменить запрос на статус, если только вы не разберетесь с этим в клиенте

Редактировать после комментария

 SELECT
    ManagerName,
    COUNT(*) AS NumberOfClients,
    COUNT(CASE WHEN S.StatusId = 1 THEN 1 ELSE NULL END) AS NumberOfClientsWithStatus1,
    COUNT(CASE WHEN S.StatusId = 2 THEN 1 ELSE NULL END) AS NumberOfClientsWithStatus2,
    COUNT(CASE WHEN S.StatusId = 3 THEN 1 ELSE NULL END) AS NumberOfClientsWithStatus3,
    ...
FROM
   Managers M ON C.ManagerId = M.ManagerId
   LEFT JOIN
   Clients C
   LEFT JOIN
   Statuses S ON C.StatusId = S.StatusId
WHERE
    M.WhenAdded BETWEEN @startDate AND @endDate
GROUP BY
    M.ManagerName
  

Если вы знаете, что statuses таблица всегда будет содержать ограниченное количество статусов, вы можете сделать это:

 SELECT M.ManagerName,
       COUNT(C.ClientId) NumberOfClients,
       SUM(CASE WHEN S.StatusId= 1 THEN 1 ELSE 0 END) NumberOfClientsWithStatus1,
       SUM(CASE WHEN S.StatusId= 2 THEN 1 ELSE 0 END) NumberOfClientsWithStatus2,
       ...
  FROM Clients C
  JOIN Managers M on M.ManagerId = C.ManagerId
  JOIN Statuses S on S.StatusId = C.StatusId
 WHERE C.WhenAdded BETWEEN startDate AND endDate 
 GROUP BY ManagerName