我正在尝试用postgres执行递归cte，但我无法理解它。就性能问题而言，表 1中只有 50 个项目，因此这应该不是问题。
表1(费用)：
id | parent_id | name
------------------------------
1 | null | A
2 | null | B
3 | 1 | C
4 | 1 | D
表 2(expense_amount)：
ref_id | amount
-------------------------------
3 | 500
4 | 200
预期成果：
id, name, amount
-------------------------------
1 | A | 700
2 | B | 0
3 | C | 500
4 | D | 200
查询
WITH RECURSIVE cte AS (
SELECT
expenses.id,
name,
parent_id,
expense_amount.total
FROM expenses
WHERE expenses.parent_id IS NULL
LEFT JOIN expense_amount ON expense_amount.expense_id = expenses.id
UNION ALL
SELECT
expenses.id,
expenses.name,
expenses.parent_id,
expense_amount.total
FROM cte
JOIN expenses ON expenses.parent_id = cte.id
LEFT JOIN expense_amount ON expense_amount.expense_id = expenses.id
)
SELECT
id,
SUM(amount)
FROM cte
GROUP BY 1
ORDER BY 1
结果
id | sum
--------------------
1 | null
2 | null
3 | 500
4 | 200
您只能对根行执行条件 sum()：
with recursive tree as (
select id, parent_id, name, id as root_id
from expense
where parent_id is null
union all
select c.id, c.parent_id, c.name, p.root_id
from expense c
join tree p on c.parent_id = p.id
)
select e.id,
e.name,
e.root_id,
case
when e.id = e.root_id then sum(ea.amount) over (partition by root_id)
else amount
end as amount
from tree e
left join expense_amount ea on e.id = ea.ref_id
order by id;
我更喜欢先执行递归部分，然后将相关表连接到递归查询的结果，但您也可以在 CTE 中连接到expense_amount。
在线示例：http://rextester.com/TGQUX53703
但是，上述内容仅在顶级父级上聚合，不适用于任何中间非叶行。
如果您还想查看中间聚合，这会变得有点复杂(并且对于大型结果可能不是很可扩展，但您说您的表没有那么大)
with recursive tree as (
select id, parent_id, name, 1 as level, concat('/', id) as path, null::numeric as amount
from expense
where parent_id is null
union all
select c.id, c.parent_id, c.name, p.level + 1, concat(p.path, '/', c.id), ea.amount
from expense c
join tree p on c.parent_id = p.id
left join expense_amount ea on ea.ref_id = c.id
)
select e.id,
lpad(' ', (e.level - 1) * 2, ' ')||e.name as name,
e.amount as element_amount,
(select sum(amount)
from tree t
where t.path like e.path||'%') as sub_tree_amount,
e.path
from tree e
order by path;
在线示例：http://rextester.com/MCE96740
查询构建属于(子)树的所有 ID 的路径，然后使用标量子选择获取属于节点的所有子行。一旦递归查询的结果无法保留在内存中，该子选择就会使它变得非常慢。
我使用level列创建了树结构的"可视化"显示 - 这有助于我调试语句并更好地理解结果。如果你在程序中需要元素的真实名称，你显然只会使用e.name而不是用空格作为前缀。
由于某种原因，我无法让您的查询正常工作。这是我的尝试，它适用于您提供的特定表(父子，没有孙子)，而无需递归。SQL 小提琴
--- step 1: get parent-child data together
with parent_child as(
select t.*, amount
from
(select e.id, f.name as name,
coalesce(f.name, e.name) as pname
from expense e
left join expense f
on e.parent_id = f.id) t
left join expense_amount ea
on ea.ref_id = t.id
)
--- final step is to group by id, name
select id, pname, sum(amount)
from
(-- step 2: group by parent name and find corresponding amount
-- returns A, B
select e.id, t.pname, t.amount
from expense e
join (select pname, sum(amount) as amount
from parent_child
group by 1) t
on t.pname = e.name
-- step 3: to get C, D we union and get corresponding columns
-- results in all rows and corresponding value
union
select id, name, amount
from expense e
left join expense_amount ea
on e.id = ea.ref_id
) t
group by 1, 2
order by 1;