使用相关层次查询（您可以在 id 列或 ROWID 伪列）：

SELECT id,
       COALESCE(
         col1,
         (
           SELECT SUM( COALESCE( col1, 0 ) )
           FROM   table_name s
           START WITH s.ROWID = t.ROWID
           CONNECT BY source_id = PRIOR id
         )
       ) AS col1,
       source_id
FROM   table_name t;

因此，对于您的示例数据：

CREATE TABLE table_name ( id, col1, source_id ) AS
SELECT 'a1',  765.3, 'a5' FROM DUAL UNION ALL
SELECT 'a2', 3298.3, 'a4' FROM DUAL UNION ALL
SELECT 'a3', 8762.1, 'a8' FROM DUAL UNION ALL
SELECT 'a4', 3298.3, null FROM DUAL UNION ALL
SELECT 'a5',   null, 'a6' FROM DUAL UNION ALL
SELECT 'a6',   null, null FROM DUAL UNION ALL
SELECT 'a7',     10, 'a5' FROM DUAL;

这将输出：

ID |   COL1 | SOURCE_ID
:- | -----: | :--------
a1 |  765.3 | a5       
a2 | 3298.3 | a4       
a3 | 8762.1 | a8       
a4 | 3298.3 | null     
a5 |  775.3 | a6       
a6 |  775.3 | null     
a7 |     10 | a5

当在层次结构的每个分支中找到第一个非空值时，可能需要停止遍历。这只需要添加一个简单的过滤器：

SELECT id,
       COALESCE(
         col1,
         (
           SELECT SUM( COALESCE( col1, 0 ) )
           FROM   table_name s
           START WITH s.ROWID = t.ROWID
           CONNECT BY source_id = PRIOR id
                  AND PRIOR col1 IS NULL
         )
       ) AS col1,
       source_id
FROM   table_name t;

（输出同上）。
db<>在这里摆弄

下一个select将为您提供那些在source\u id列中有引用，在col1列中有值的行。

select id 
       , case when col1 is null then
              (select sum(col1) from test tt where tt.sourceid = t.id)
         else
              col1
         end result
       , sourceid
from test t     
order by id;

这是一个演示
这是完整解决方案的一个选项：

with cte as (select t.id 
       , case when t.col1 is null then
              (select sum(tt.col1) from test tt where tt.sourceid = t.id)
         else
              t.col1
         end result
       , t.sourceid
from test t     
order by id)
select t.id 
       , 
          case when t.result is null then
              (select sum(tt.result) from cte tt where tt.sourceid = t.id)
         else
              t.result
         end result
       , t.sourceid
from cte t     
order by id

这是一个演示