Блог

снова у меня небольшая (я надеюсь, небольшая) проблема.

У меня есть иерархия родительских элементов, в которой у одного родителя может быть несколько дочерних элементов, а у дочернего элемента может быть снова несколько дочерних элементов и так далее.

у каждого родителя и дочернего элемента есть сумма (значение), и родитель может компенсировать любое недостающее количество дочерних элементов.

Вот моя таблица:

 CREATE TABLE #Test
(
 ID INTEGER NOT NULL
,ParentID INTEGER
,NAME VARCHAR(20)
,value INTEGER
)
  

Тестовые данные:

INSERT INTO #Test
( ID, ParentID, NAME, value )
VALUES ( 1, NULL, 'MainStore', 1 )
, ( 2, 1, 'Substore1', 3 )
, ( 3, 1, 'Substore2', 10 )
, ( 4, 2, 'Sub1Substore1', -1 )
, ( 5, 2, 'Sub1Substore2', 1 )
, ( 6, 3, 'Sub2Substore1', 10 )

Чтобы отобразить родительскую дочернюю реализацию, я попробовал это с помощью CTE:

 ;WITH    CTE
      AS ( SELECT   ID
                   ,ParentID
                   ,Name
                   ,Value
                   ,0 AS LEVEL
                   ,CAST('' AS INTEGER) AS ID_Parent
           FROM     #Test
           WHERE    ParentID IS NULL
           UNION ALL
           SELECT   child.ID
                   ,child.ParentID
                   ,child.Name
                   ,child.Value
                   ,parent.Level   1
                   ,parent.ID
           FROM     CTE parent
                    JOIN #Test child ON child.ParentID = parent.ID
         )
  

Как вы можете видеть, Substore1 имеет 2 дочерних элемента (Sub1Substore1 и Sub1Substore2) Substore1 имеет значение 3, Sub1Substore1 -1 и Sub1Substore2 имеет 1.

Sub1Substore1 является дочерним элементом Substore1, и родительский элемент может компенсировать недостающие значения дочерних элементов.

Мой желаемый результат должен выглядеть следующим образом:

 ID          ParentID    Name                 Value       LEVEL       ID_Parent   FreeValues
----------- ----------- -------------------- ----------- ----------- ----------- -----------
1           NULL        MainStore            1           0           0           1
2           1           Substore1            3           1           1           2
3           1           Substore2            10          1           1           8
4           2           Sub1Substore1        -1          2           2           0
5           2           Sub1Substore2        1           2           2           1
6           3           Sub2Substore1        -2          2           3           0
  

К сожалению, веб-сайт SQL Fiddle в данный момент у меня не работает, но я предоставлю этот пример позже на SQL Fiddle.

РЕДАКТИРОВАТЬ: полностью переписал ответ из-за неправильного понимания задачи.

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

Однако вот немного менее элегантное решение, которое должно сработать за вас. Обратите внимание, что я предположил, что идентификатор родительского элемента всегда меньше, чем у его прямых дочерних элементов. Это может стать проблемой, если вы должны иметь возможность изменять родительский элемент уже вставленной строки «на лету». В любом случае, вот так:

 --Declare temp table.
DECLARE @Temp TABLE
(
 ID INTEGER NOT NULL
,ParentID INTEGER
,NAME VARCHAR(20)
,value INTEGER
,FreeValues INTEGER
,NeedFromParent INTEGER
,ChildrenNeed INTEGER
);

--Other variables
DECLARE @ID INTEGER
DECLARE @ParentID INTEGER
DECLARE @Name VARCHAR(20)
DECLARE @value INTEGER
DECLARE @FreeValues INTEGER
DECLARE @NeedFromParent INTEGER
DECLARE @ChildrenNeed INTEGER

--Loop with cursor to calculate FreeValues
DECLARE cur CURSOR FOR SELECT id, parentId, Name, Value FROM #test 
ORDER BY ID DESC -- NOTE! Assumed that Parent's ID < Child's ID.
OPEN cur
FETCH NEXT FROM cur INTO @ID, @ParentID, @Name, @value
WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @ChildrenNeed = CASE
   WHEN SUM(temp.NeedFromParent) IS NULL THEN 0 
   ELSE SUM(temp.NeedFromParent) END
  FROM @temp temp WHERE temp.ParentID=@ID AND temp.NeedFromParent > 0

  IF @ChildrenNeed IS NULL SET @ChildrenNeed = 0

  IF @Value - @ChildrenNeed < 0 
    SET @NeedFromParent = @Value - @ChildrenNeed
  ELSE
    SET @NeedFromParent = 0

  SET @NeedFromParent = -@NeedFromParent

  IF @NeedFromParent = 0 
    SET @FreeValues = @value - @ChildrenNeed
  ELSE
    SET @FreeValues = 0

  INSERT INTO @Temp 
    VALUES(@ID, @ParentID, @Name, @value, @FreeValues, @NeedFromParent, @ChildrenNeed)
  FETCH NEXT FROM cur INTO @ID, @ParentID, @Name, @value
END 
CLOSE cur;
DEALLOCATE cur;

-- Join with recursively calculated Level.
;WITH CTE
 AS ( SELECT ID ,ParentID,0 AS [Level]
      FROM  #Test WHERE ParentID IS NULL
      UNION ALL
      SELECT child.ID,child.ParentID,parent.Level   1
      FROM CTE parent INNER JOIN #Test child ON child.ParentID = parent.ID
   ) 
SELECT t1.ID, t1.ParentID, t1.Name, t1.Value, cte.[Level], t1.FreeValues 
FROM CTE cte LEFT JOIN @temp t1 ON t1.ID = cte.ID 
ORDER BY ID