Total number of children based on parenting
I have 2 tables: table1
.table2
Parent Child Point Parent Total
a b 100 a 0(default) (result = 1050)
b c 200 b 0 (result = 950)
c d 250 c 0 (result = 750)
d e 500
The result in table2
must be the sum of the child points based on the parent in table1
.
a---b---c---d---e
I tried many times but couldn't figure it out.
UPDATE table2 set Total=???
0
user3835545
source
to share
2 answers
Use a recursive CTE :
WITH RECURSIVE cte AS (
SELECT parent, child, point AS total
FROM tbl1
UNION ALL
SELECT c.parent, t.child, c.total + t.point
FROM cte c
JOIN tbl1 t ON t.parent = c.child
)
SELECT *
FROM cte
+2
Erwin Brandstetter
source
to share
It hurts my brain ... The following should work for you, please note that this is very crude and you will want to simplify it.
DECLARE @parent NCHAR(1), @child NCHAR(1), @runningTotal INT
SET @parent = 'a' -- set starting parent here
DECLARE myCursor CURSOR FOR SELECT [Parent], [Child], [Point] FROM table1 WHERE [Parent] = @parent
OPEN myCursor
FETCH NEXT FROM myCursor INTO @parent, @child, @runningTotal
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS (SELECT * FROM table1 WHERE [Parent] = @child)
BEGIN
DECLARE @point INT
SELECT @parent = [Parent], @child = [Child], @point = [Point] FROM table1 WHERE [Parent] = @child
SET @runningTotal = @runningTotal + @point
END
ELSE
BEGIN
BREAK
END
END
CLOSE myCursor
DEALLOCATE myCursor
SELECT @runningTotal
0
Xeon
source
to share