希望使用“分组依据”添加到现有工作查询的计数查询中

Question

目标：我希望获取重新分配工作项的次数据我了解，正确的查询如下：

SELECT      
    WorkItemDimvw.Id, 
    COUNT(WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey) AS Assignments    
FROM  WorkItemDimvw INNER JOIN  WorkItemAssignedToUserFactvw 
ON WorkItemDimvw.WorkItemDimKey = WorkItemAssignedToUserFactvw.WorkItemDimKey 
GROUP BY WorkItemDimvw.Id

现有查询如下，我想知道/忘记我是否应该：

1. 只需添加COUNT(WorkItemAssignedToUserFactvw.WorkItemAssignedToUser_UserDimKey) AS Assignments，因为联接是存在的，除了它是按 WorkItemDimvw.Id 分组
的
2. 它应该改为下面的选择中的子查询吗？

查询：

SELECT 
    SRD.ID,
    SRD.Title,
    SRD.Description,
    SRD.EntityDimKey,
    WI.WorkItemDimKey,
    IATUFact.DateKey
FROM 
    SLAConfigurationDimvw 
    INNER JOIN SLAInstanceInformationFactvw 
        ON SLAConfigurationDimvw.SLAConfigurationDimKey = SLAInstanceInformationFactvw.SLAConfigurationDimKey 
    RIGHT OUTER JOIN ServiceRequestDimvw AS SRD 
    INNER JOIN WorkItemDimvw AS WI 
        ON SRD.EntityDimKey = WI.EntityDimKey
    LEFT OUTER JOIN WorkItemAssignedToUserFactvw AS IATUFact 
        ON WI.WorkItemDimKey = IATUFact.WorkItemDimKey 
        AND IATUFact.DeletedDate IS NULL 

Answer 1

诀窍是在加入子查询之前聚合子查询上的数据。

SELECT 
    SRD.ID,
    SRD.Title,
    SRD.Description,
    SRD.EntityDimKey,
    WI.WorkItemDimKey,
    IATUFact.DateKey,
    IATUFact.Assignments
FROM 
    SLAConfigurationDimvw 
INNER JOIN
    SLAInstanceInformationFactvw 
        ON SLAConfigurationDimvw.SLAConfigurationDimKey = SLAInstanceInformationFactvw.SLAConfigurationDimKey 
RIGHT OUTER JOIN
    ServiceRequestDimvw AS SRD
        ON <you're missing something here>
INNER JOIN
    WorkItemDimvw AS WI 
        ON SRD.EntityDimKey = WI.EntityDimKey
LEFT OUTER JOIN
(
    SELECT
        WorkItemDimKey,
        DateKey,
        COUNT(WorkItemAssignedToUser_UserDimKey) AS Assignments
    FROM
        WorkItemAssignedToUserFactvw
    WHERE
        DeletedDate IS NULL
    GROUP BY
        WorkItemDimKey,
        DateKey
)
    IATUFact
        ON  WI.WorkItemDimKey = IATUFact.WorkItemDimKey