sql - 按优先级连接和父级连接

我认为您需要以另一种方式走到树上，然后可以sys_connect_by_path()用来连接段名称，并connect_by_isleaf仅显示叶节点：

select col_a, col_b
from (
  select ltrim(sys_connect_by_path(seg, ' - '), ' - ') as col_a,
    seg_id as col_b,
    connect_by_isleaf as isleaf
  from my_table
  start with seg = 'Prepaid'
  connect by parent_seg_id = prior seg_id
)
where isleaf = 1;

COL_A                                                   COL_B
-------------------------------------------------- ----------
Prepaid                                                361977
Prepaid - Voice Mobile                             2687522995
Prepaid - Internet Mobile                          2687525564
Prepaid - Data Mobile                              2687527808
Prepaid - Voice Fixa - Default                     2687531979
Prepaid - Voice Fixa - Internal PABX               2687534794

使用ltrim()可以删除-路径开头包含的其他内容。

但是你需要一个开始的条件。我已经使用了“预付费”，它在输出中包括一个额外的行，但是您可以start with seg_id = 361978改为选择其他条件，具体取决于您实际想要查看的数据。

您还可以使用考虑递归子查询分解。