
Zitat von
GENiALi
Habe noch was bemerkt.
Ich muss nicht eine Nummer zurück haben sondern die ID des root Nodes mit dessen Summe.
Code:
test=*# select * from nodes;
id | parent | value
----+--------+-------
1 | | 1
2 | 1 | 2
3 | 2 | 3
4 | 1 | 4
5 | | 100
6 | 5 | 101
7 | 6 | 102
(7 rows)
test=*# select id, sum(value) from (with recursive r as (select id, parent, row_number() over () as root_number, value from nodes where parent is null union all select n.id, n.parent, r.root_number, n.value from nodes n inner join r on (n.parent=r.id)), n as (select id, root_number from r where parent is null) select r.root_number, r.value, n.id from r left join n on r.root_number=n.root_number) foo group by id;
id | sum
----+-----
1 | 10
5 | 303
(2 rows)
Andreas
Lesezeichen