帮助解决困难的“分组依据”条款

Question

需要您的帮助来进行查询。

我有一个表 Managers (ManagerId, ManagerName)

我有一个表 Statuses (StatusId, StatusName)
（该表中有大约 10 个状态）

我有一个表 Clients (ClientId, ClientName, ManagerId, StatusId, WhenAdded)
（WhenAdded 是日期时间类型）

很明显，字段 'ManagerId' 引用了表 'Managers' 并且字段'StatusId'引用表'Statuses'。

用户希望获取下表中一段时间​​内（从 startDate 到 endDate，使用字段“WhenAdded”）有关经理的一些统计信息。

列：

ManagerName、NumberOfClients、NumberOfClientsWithStatus1、NumberOfClientsWithStatus2、NumberOfClientsWithStatus3 等。

名称为 NumberOfClientsWithStatusI 的列数，其中 i 是等于表 'Statuses' 中的行数的状态数。

我怎样才能做到这一点？

t-sql，sql server 2008 r2 快捷版。

Answer 1

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

Answer 2

如果您知道 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