递归查询和计数（SQL Server）

I'm trying to write a query using these four simplified tables:

我正在尝试使用这四个简化表编写查询:

Organization
(pk) OrganizationID
OrganizationName
(fk) ParentOrganizationID

组织(pk)OrganizationID OrganizationName(fk)ParentOrganizationID

Personnel
(pk) PersonnelID
(fk) OrganizationID
Name

人员(pk)PersonnelID(fk)OrganizationID名称

Event
(pk) EventID
EventName

事件(pk)EventID EventName

EventLog
(pk) PersonnelID
(pk) EventID
TimeOfParticipation

EventLog(pk)PersonnelID(pk)EventID TimeOfParticipation

I want to create a query that takes an eventID and an organizationID as a paramater and returns a table that returns all of the organization's name, the total number in the organization and children organizations, and the total number of participants in the event for the organization and it's children. An example return might be:

我想创建一个查询,它将eventID和organizationID作为参数,并返回一个表,该表返回组织的所有名称,组织和子组织中的总数,以及组织事件中参与者的总数这是孩子们。返回的示例可能是:

OrganizationName   | TotalNumberInOrganization | TotalParticipatingInEvent  
TopOrganization    |         200               |            150      
 SecondTier1       |         150               |            100 
  Tier1Child       |          50               |             50
  Tier1Child2      |          50               |             25
 SecondTier2       |          25               |             25

The top organization is the sum of all of its children, SecondTier1 and SecondTier2, and itself. SecondTier1 is the sum of all of it's children, Teir1Child and Tier1Child2, and itself. This would go on calculating all of the children and totals.

最高组织是其所有子项的总和,SecondTier1和SecondTier2,以及它们本身。 SecondTier1是它的所有子节点Teir1Child和Tier1Child2的总和。这将继续计算所有的孩子和总数。

I know how to return just one entry using a recursive CTE, for example just the total for the top organization, but I'm not sure how to go about getting the totals for all of the organizations and their children. Any help would be appreciated.

我知道如何使用递归CTE返回一个条目,例如只是*组织的总数,但我不知道如何获得所有组织及其子组的总数。任何帮助,将不胜感激。

As requested here is the procedure I use to return a single row about an organization.

这里要求的是我用来返回关于组织的单行的过程。

For some reason if I add the 'U' in UNION it throws me a network error and won't let me edit it.

出于某种原因,如果我在UNION中添加“U”,它会引发网络错误并且不允许我编辑它。

@OrganizationID uniqueidentifier
@EventID uniqueidentifier

WITH OrganizationList(OrganizationID) AS
    (SELECT Organization.OrganizationID
    FROM Organization
    WHERE OrganizationID = @OrganizationID
    NION ALL
    SELECT Organization.OrganizationID
    FROM Organization 
    INNER JOIN OrganizationList ON Organization.ParentOrganizationID = OrganizationList.OrganizationID)

SELECT OrganizationAbbreviation,

       (SELECT COUNT(*)
        FROM Personnel
        WHERE Personnel.OrganizationID IN (SELECT OrganizationID FROM OrganizationList))
        AS OrganizationTotal,

        (SELECT COUNT(*)
         FROM Personnel 
         INNER JOIN EventLog ON EventLog.PersonnelID = Personnel.PersonnelID
         WHERE Personnel.OrganizationID IN (SELECT OrganizationID FROM OrganizationList)
               AND EventLog.EventID = @EventID)
         AS TotalPresent
FROM Organization
WHERE OrganizationID = @OrganizationID

1 个解决方案

#1

I think this will work for you:

我认为这对你有用:

WITH OrganizationTree (RootOrganizationID, OrganizationID)
AS
(
--Anchor
    SELECT  O.OrganizationID, O.OrganizationID
    FROM    Organization O
    UNION ALL
--Recurse
    SELECT  T.RootOrganizationID, O.OrganizationID
    FROM    OrganizationTree T
    JOIN    Organization O
        ON  O.ParentOrganizationId = T.OrganizationID
)
--execute
SELECT  P.OrganizationName, 
        SUM(ISNULL(PPL.NumberInOrganization, 0)) AS TotalNumberInOrganization, 
        SUM(ISNULL(EVT.NumberParticipatingInEvent, 0)) AS TotalNumberParticipatingInEvent
FROM    OrganizationTree T
JOIN    Organization P
        ON T.RootOrganizationID = P.OrganizationID
LEFT
JOIN    
(
        SELECT  P.OrganizationID, 
                COUNT(*) AS NumberInOrganization
        FROM    Personnel P
        GROUP BY P.OrganizationID
) PPL
        ON  PPL.OrganizationID = T.OrganizationID
LEFT
JOIN
(
        SELECT  P.OrganizationID,
                COUNT(*) AS NumberParticipatingInEvent
        FROM    EventLog EL
        JOIN    Personnel P
                ON  EL.PersonnelID = P.PersonnelID
        GROUP BY P.OrganizationID
) EVT
        ON  EVT.OrganizationID = T.OrganizationID
GROUP BY T.RootOrganizationID, P.OrganizationName

If you need to have the indentation like in your example output, then this should work:

如果你需要像示例输出中那样缩进,那么这应该工作:

WITH OrganizationTree (RootOrganizationID, OrganizationID)
AS
(
--Anchor
    SELECT  O.OrganizationID, O.OrganizationID
    FROM    Organization O
    UNION ALL
--Recurse
    SELECT  T.RootOrganizationID, O.OrganizationID
    FROM    OrganizationTree T
    JOIN    Organization O
        ON  O.ParentOrganizationId = T.OrganizationID
)
--execute
SELECT  SPACE(L.OrganizationLevel) + P.OrganizationName AS FormattedOrganizationName,
        P.OrganizationName, 
        SUM(ISNULL(PPL.NumberInOrganization, 0)) AS TotalNumberInOrganization, 
        SUM(ISNULL(EVT.NumberParticipatingInEvent, 0)) AS TotalNumberParticipatingInEvent
FROM    OrganizationTree T
JOIN    
(
        SELECT  L.OrganizationID,
                (COUNT(*) - 1) AS OrganizationLevel
        FROM    OrganizationTree L
        GROUP BY L.OrganizationID
) L
    ON  T.RootOrganizationID = L.OrganizationID
JOIN    Organization P
        ON T.RootOrganizationID = P.OrganizationID
LEFT
JOIN    
(
        SELECT  P.OrganizationID, 
                COUNT(*) AS NumberInOrganization
        FROM    Personnel P
        GROUP BY P.OrganizationID
) PPL
        ON  PPL.OrganizationID = T.OrganizationID
LEFT
JOIN
(
        SELECT  P.OrganizationID,
                COUNT(*) AS NumberParticipatingInEvent
        FROM    EventLog EL
        JOIN    Personnel P
                ON  EL.PersonnelID = P.PersonnelID
        GROUP BY P.OrganizationID
) EVT
        ON  EVT.OrganizationID = T.OrganizationID
GROUP BY T.RootOrganizationID, L.OrganizationLevel, P.OrganizationName

#1