sql - 如何获取Oracle数据库中的根记录

Question

从通用节点（或叶子）开始如何获取根节点？

SELECT 
    ID,MSG_ID,PARENT_ID 
FROM 
    TABLE_X
CONNECT BY PRIOR PARENT_ID = ID;

ID  MSG_ID                              PARENT_ID
4   3                                   NULL
5   93bea0f71b07-4037-9009-f148fa39bb62 4
4   3                                   NULL
6   6f5f5d4ab1ec-4f00-8448-7a6dfa6461b2 4
4   3                                   NULL    
7   3                                   NULL
8   7e0fae569637-4d29-9075-c273eb39ae8e 7
7   3                                   NULL
9   8a3e7485b3e8-45b1-a31d-c52fd32111c0 7
7   3                                   NULL
10  fcc622d5af92-4e61-8d7c-add3da359a8b 7
7   3                                   NULL

如何获取根 msg_id？

标签： sqloracletreerecursive-queryhierarchical-query

Answer 1

您可以使用递归查询：

with cte (id, msg_id, parent_id) as (
    select id, msg_id, parent_id, from mytable where id = ?
    union all
    select t.id, t.msg_id, t.parent_id
    from mytable t
    inner join cte c on c.parent_id = t.id
)
select * from cte where parent_id is null