Member 10967983 Ответов: 1

Получить несколько сумм в древовидной структуре

У меня есть древовидная структура в таблице employees (id, name, parentid), и эта таблица может быть вложенной.employees-это отношение один ко многим к другой таблице Sales со столбцами(id, employeeid, quantity). У каждого сотрудника есть количество продаж. Я хочу рассчитать сумму количества для каждого сотрудника вместе с дочерними сотрудниками. Я написал некоторый код, чтобы быть более ясным.

DECLARE @Employees TABLE(ID INT, Name NVARCHAR(100), ParentID INT);
DECLARE @Sales TABLE(ID INT, EmployeeID INT, Quantity INT);    

INSERT INTO @Employees(ID, Name, ParentID)VALUES
(1,N'Employee1', NULL),
(2,N'Employee2', 1),
(3,N'Employee3', 2),
(4,N'Employee4', NULL),
(5,N'Employee5', 4),
(6, N'Employee6', 5)

INSERT INTO @Sales(ID, EmployeeID, Quantity)VALUES
(1,1,4),
(2,1,2),
(3,2,3),
(4,3,2),
(5,3,7),
(6,5,8),
(7,5,3),
(8,6,2)

Я присоединился к этим столам и выглядит это так: Объединенные Таблицы

Что я уже пробовал:

Вот мой запрос
;WITH cte 
AS
(
  SELECT e.ID, e.Name, e.ParentID FROM @Employees e
  WHERE e.ParentID IS NULL
  UNION ALL
  SELECT  e.ID, e.Name, e.ParentID  FROM @Employees e
    INNER JOIN cte c ON c.ID = e.ParentID
)
SELECT
     c.ID
    ,c.Name
    ,c.ParentID
    ,ISNULL(SUM(s.Quantity), 0) AS ParentSumSales
    ,ISNULL(LEAD(SUM(s.Quantity)) OVER(ORDER BY c.ID), 0) AS ChildSumSales
FROM cte c
    LEFT JOIN @Sales s ON s.EmployeeID = c.ID
GROUP BY c.ID, c.Name, c.ParentID


запрос возвращает этот результат, но он неверен. Возвращаемый результат

Возвращаемый результат должен быть таким:
ID     Name        ParentSumSales    ChildSumSales
---    ---------   -------------     -------------
1      Employee1   6                 12
2      Employee2   3                 9   
3      Employee3   9                 0
4      Employee4   0                 13
5      Employee5   11                2
6      Employee6   2                 0


Как я могу написать запрос, чтобы получить эти данные?

RossMW

Возможно, вам придется переосмыслить то, чего вы пытаетесь достичь. В своем запросе вы пытаетесь суммировать значения из рекурсивного цикла, но этот цикл может иметь один или несколько рекурсивных уровней. Я не вижу, как это достигается в операторе select. Чтобы достичь этого чисто в SQL, вам, вероятно, понадобится табличная функция, но она может быть сложной и медленной. Ниже приведен пример рекурсивной функции для получения самого верхнего employeeid данного сотрудника в качестве руководства или отправной точки.

< pre lang= "SQL">
Создайте функцию dbo.FindParent (@ID int)
возвращает значение типа int

АС
НАЧАТЬ

Объявить @parent int
Объявить @tmp int
Объявить @counter int
set @tmp = @ID
set @parent = @ID
SET @counter = 1
В то время как @counter < 10 и @tmp не являются нулевыми
НАЧАТЬ
Выберите @ТМП = атрибутом parentId, @родитель = ID от сотрудника где ID = @ТМП
SET @counter = @counter + 1
конец
возвращение @родитель;
конец
ГО
< / pre>

1 Ответов

Рейтинг:
2

Richard Deeming

Возможно, есть более чистый способ сделать это, но предполагая, что вы используете MS SQL Server 2008 или более позднюю версию, тип данных hierarchyid[^] обеспечивает решение:

WITH cteRawEmployees As
(
    SELECT
        E.ID,
        E.ParentID,
        E.Name,
        IsNull((
            SELECT Sum(S.Quantity) 
            FROM @Sales As S 
            WHERE S.EmployeeID = E.ID
        ), 0) As Sales
    FROM
        @Employees As E
),
cteEmployeeTree As
(
    SELECT
        R.ID,
        R.Name,
        R.Sales,
        CAST('/' + CAST(R.ID As varchar(10)) + '/' As varchar(max)) As NodePath
    FROM
        cteRawEmployees As R
    WHERE
        R.ParentID Is Null

    UNION ALL

    SELECT
        R.ID,
        R.Name,
        R.Sales,
        CAST(E.NodePath + CAST(R.ID As varchar(10)) + '/' As varchar(max))
    FROM
        cteRawEmployees As R
        INNER JOIN cteEmployeeTree As E
        ON E.ID = R.ParentID
),
cteEmployees As
(
    SELECT
        ID,
        Name,
        Sales,
        CAST(NodePath As hierarchyid) As NodePath
    FROM
        cteEmployeeTree
)
SELECT
    E.ID,
    E.Name,
    E.Sales As ParentSumSales,
    IsNull((
        SELECT Sum(Sales) 
        FROM cteEmployees As E2 
        WHERE E2.NodePath.IsDescendantOf(E.NodePath) = 1
        And E2.ID != E.ID -- NB: IsDescendantOf considers a node to be its own descendant.
    ), 0) As ChildSumSales
FROM
    cteEmployees As E
ORDER BY
    E.NodePath
;

Выход:
ID   Name         ParentSumSales    ChildSumSales
--   ---------    --------------    -------------
1    Employee1    6                 12
2    Employee2    3                 9
3    Employee3    9                 0
4    Employee4    0                 13
5    Employee5    11                2
6    Employee6    2                 0

Maciej Los

Выглядит идеально!
5!