Рекурсивный запрос и подсчет (SQL Server)

Я пытаюсь написать запрос, используя эти четыре упрощенные таблицы:

Организация
(pk) OrganizationID
OrganizationName
(fk) ParentOrganizationID

Персонал
(pk) PersonnelID
(fk) OrganizationID
Имя

Событие
(pk) EventID
EventName

EventLog
(pk) PersonnelID
(pk) EventID
TimeOfParticipation

Я хочу создать запрос, который принимает идентификатор события и идентификатор организации в качестве параметра и возвращает таблицу, которая возвращает все название организации, общее количество в организации и дочерних организациях, а также общее количество участников события для организации и это дети. Пример возврата может быть:

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

Высшая организация — это сумма всех своих дочерних элементов, SecondTier1 и SecondTier2, и самой себя. SecondTier1 — это сумма всех его дочерних элементов, Teir1Child и Tier1Child2, и самого себя. Это будет продолжаться при вычислении всех детей и итогов.

Я знаю, как вернуть только одну запись с помощью рекурсивного CTE, например, только итог для верхней организации, но я не уверен, как получить итоги для всех организаций и их дочерних элементов. Любая помощь будет оценена по достоинству.

В соответствии с просьбой, вот процедура, которую я использую для возврата одной строки об организации.

По какой-то причине, если я добавлю «U» в UNION, это выдает мне сетевую ошибку и не позволяет мне редактировать ее.

@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

sql sql-server recursion count recursive-query
person Scott    schedule 21.03.2011    source источник
comment
Проблема в том, что рекурсивные CTE в SQL Server имеют некоторые ограничения. В частности, рекурсивная часть не может содержать группировку и, следовательно, агрегаты. По этой причине мне очень хотелось бы знать, каким может быть ваше решение для одной записи, особенно для более высокого уровня. Не могли бы вы опубликовать это? У кого-то здесь может быть хорошая идея о том, как превратить его в полное решение для вашего случая.   -  person Andriy M    schedule 22.03.2011

Ответы (1)


arrow_upward
2
arrow_downward

Я думаю, что это сработает для вас:

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

Если вам нужно иметь отступ, как в вашем примере, то это должно работать:

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
person Fergus Bown    schedule 25.03.2011
comment
Я не уверен, хотите ли вы, чтобы TotalNumberParticipatingInEvent был «Количество отдельных лиц, участвующих в одном или нескольких мероприятиях» или «сумма лиц, участвующих в каждом мероприятии». Приведенный выше запрос делает последнее, но его легко изменить на первый - просто измените подзапрос... - person Fergus Bown; 25.03.2011
comment
Я не уверен, что у этой техники есть конкретное название - по крайней мере, я не знаю...! - person Fergus Bown; 05.04.2011