SQL层级查询需求：获取层级列值并保留初始查询ID至所有结果行

要实现将每个层级分支的起始ID（即START WITH中指定的ID）显示在对应分支的所有记录里，只需借助Oracle层级查询的CONNECT_BY_ROOT函数即可完成。

表结构与测试数据 #

首先是表创建和数据插入语句：

CREATE TABLE user_test (id int, parentid int);

INSERT INTO USER_TEST (ID, PARENTID) VALUES (80,40);
INSERT INTO USER_TEST (ID, PARENTID) VALUES (40,20);
INSERT INTO USER_TEST (ID, PARENTID) VALUES (20,10);
INSERT INTO USER_TEST (ID, PARENTID) VALUES (100,50);
INSERT INTO USER_TEST (ID, PARENTID) VALUES (50,25);
INSERT INTO USER_TEST (ID, PARENTID) VALUES (25,18);

修改后的查询语句 #

在原查询基础上添加CONNECT_BY_ROOT id作为第三列，该函数会返回当前层级树的根节点（也就是START WITH中指定的起始ID）：

SELECT
    id,
    parentid,
    CONNECT_BY_ROOT id AS root_id
FROM
    user_test
START WITH
    id IN (80, 100, 50)
CONNECT BY
    PRIOR parentid = id ;

查询结果 #

执行上述语句后，输出将与期望一致：

50  25     50
    25  18     50 
    
    80  40     80
    40  20     80
    20  10     80
    
    100 50     100
    50  25     100
    25  18     100

说明 #

CONNECT_BY_ROOT是Oracle专为层级查询设计的函数，它能捕获当前行所在层级树的最顶层节点值，正好满足我们需要将起始ID关联到对应分支所有记录的需求。

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