sql - 递归添加多行

Question

我有一个名为river 的表，其中包含三列:name、river 和length。

name 是河流的名称，river 是这条河流所连接的河流，length 是河流的长度

示例表可能如下所示:

name       | river      | length
           |            |  
Amazonas   |            | 3778
Rio negro  | Amazonas   | 2886
Huallaga   | Rio Negro  | 1138
Nile       |            | 3090
White Nile | Sobat      | 950
Sobat      | White Nile | 740

我想递归将我在这里完成的河流的长度加在一起:

with recursive cte as(
select name, river,length from river
where name = 'Amazonas'
Union all
select r.name,r.river,r.length from river r
join cte s on r.river = s.name
)
select sum(length) from cte;

但是我也希望能够将不同的父河流加在一起并像这样对它们进行相应的分组:

name       combined_length
Amazonas | 7802
Nile     | 6203
Volga    | 6234

我已经尝试将 或 语句添加到上面的代码中，如下所示:

where name = 'Amazonas' or name = 'Nile' or name = 'Volga'

但是我无法按它们的父级对它们进行分组，我只能将每条河流的所有长度加在一起(亚马逊河、尼罗河和伏尔加河的总和)，或者如果我尝试使用分组依据则根本不添加它们。

我还尝试使用以下三个不同的子查询:

length from river
where name = 'Amazonas'
Union all
select r.name,r.river,r.length from river r
join cte s on r.river = s.name

并用我想要的河流替换名称，但随后出现错误:

recursive query "cte" does not have the form non-recursive-term UNION [ALL] recursive-term

Answer 1

我期待这样的事情来获得所有父河流的长度:

with recursive cte as (
      select name, river, length, name as parent
      from river r
      where r.river is null
      union all
      select r.name, r.river, r.length, cte.parent
      from river r join
           cte s
           on r.river = s.name
     )
select parent, sum(length)
from cte
group by parent;