表结构如下：
pid id sl
a a1 2
a b1 1
a1 a11 2
a1 a12 3
b1 a11 1
b1 b11 1
统计结果
pid id sl
a a11 5
a a12 6
a b11 1
------解决思路----------------------
WITH table1(pid,id,sl) AS (
SELECT 'a','a1',2 UNION ALL
SELECT 'a','b1',1 UNION ALL
SELECT 'a1','a11',2 UNION ALL
SELECT 'a1','a12',3 UNION ALL
SELECT 'b1','a11',1 UNION ALL
SELECT 'b1','b11',1
)
,tree AS (
SELECT pid rootid, *
FROM table1
WHERE NOT EXISTS (SELECT * FROM table1 t WHERE t.id = table1.pid)
UNION ALL
SELECT p.rootid, c.pid, c.id, p.sl + c.sl
FROM tree p
JOIN table1 c
ON c.pid = p.id
)
,leaf AS (
SELECT *
FROM tree l
WHERE NOT EXISTS (SELECT * FROM tree c WHERE l.id = c.pid)
)
SELECT rootid pid, id, sum(sl) sl
FROM leaf
GROUP BY rootid, id
pid id sl
---- ---- -----------
a a11 6
a a12 5
a b11 2