#sql #sql-server #sum #common-table-expression
#sql #sql-server #сумма #common-table-expression
Вопрос:
снова у меня небольшая (я надеюсь, небольшая) проблема.
У меня есть иерархия родительских элементов, в которой у одного родителя может быть несколько дочерних элементов, а у дочернего элемента может быть снова несколько дочерних элементов и так далее.
у каждого родителя и дочернего элемента есть сумма (значение), и родитель может компенсировать любое недостающее количество дочерних элементов.
Вот моя таблица:
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.
Комментарии:
1. Что именно такое column FreeValues? И зачем удвоение родительского идентификатора?
2. Свободные значения — это значения, которые я хочу вычислить. Sub1Substore2 имеет значение = 1, и ему не нужно компенсировать дочерние элементы, поэтому freeValue = 1, Substore1 имеет два дочерних элемента, и один из них имеет значение = -1, поэтому ему нужно компенсировать это. Значение Sbstore1 = 3 — значение для дочернего элемента = свободное значение 2. Двойной столбец для родительского идентификатора не имеет особой причины. извините.
Ответ №1:
РЕДАКТИРОВАТЬ: полностью переписал ответ из-за неправильного понимания задачи.
Это могло бы быть элегантно решаемо с помощью общего табличного выражения, но поскольку 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
Комментарии:
1. Спасибо, что мне помогает, и да, -2 правильно. Извините 🙂 Но остается одна небольшая проблема .. если я добавлю другого дочернего элемента для Sub1Substore1 ( 7, 4, ‘Sub1Sub1Substore1’, -1 ) со значением -1, то родительский элемент с идентификатором 2 (Substore1) также должен компенсировать это. на данный момент это работает только на один уровень ниже. я знаю, что я тоже некоторое время ломаю голову над этим :/