Oracle中使用CONNECT BY时如何保留文档ID等额外值？

问题背景 #

已知：

• 视图v_documents仅存储每个文档的最新flow_id，但文档存在多个历史flow_id
• 视图v_flow_ids通过flow_id和parent_id，可从最新flow_id追溯该文档的所有关联flow_id
• 当前批量处理多个文档时，现有SQL无法追踪每个flow_id所属的文档ID，现有SQL代码如下：

select ff.flow_id, max(ff.repo_id) as repo_id --d.id would also be necessary here
from (select f.flow_id
      from v_flow_ids f  
      start with f.flow_id in (select d.flow_id
                               from v_documents d
                               where d.id in (28211,14631,28212,14632,28192))
      connect by prior f.parent_id = f.flow_id) t
left join v_flow_additional_info ff on t.flow_id = ff.flow_id
group by ff.flow_id, ff.external_id

需求：能否在最终查询结果中获取对应的v_documents.id，让每个flow_id都关联到所属的文档ID？

解决方案 #

可以实现，核心是在递归查询阶段就绑定原始文档ID，确保后续所有追溯到的flow_id都能关联到所属文档。修改后的SQL如下：

select 
    ff.flow_id, 
    max(ff.repo_id) as repo_id,
    t.doc_id as document_id
from (
    select 
        f.flow_id,
        d.id as doc_id
    from v_flow_ids f
    inner join v_documents d 
        on f.flow_id = d.flow_id
    start with d.id in (28211,14631,28212,14632,28192)
    connect by prior f.parent_id = f.flow_id
) t
left join v_flow_additional_info ff 
    on t.flow_id = ff.flow_id
group by ff.flow_id, ff.external_id, t.doc_id

关键修改说明 #

• 递归阶段绑定文档ID：将原start with中的子查询改为直接关联v_documents，在递归初始步骤就获取并保留文档ID（d.id），这样递归过程中每个flow_id都会携带所属文档的ID
• 分组时包含文档ID：最终分组操作加入t.doc_id，保证聚合后的结果不会丢失文档关联关系
• 逻辑更清晰：用内连接替代原IN子查询，避免子查询的嵌套层级，提升可读性和执行效率

修改后，查询结果将同时返回flow_id、repo_id以及对应的document_id，解决批量处理时的归属追踪问题。

内容的提问来源于stack exchange，提问作者1234567