以下是DB2的例子WITH test(parentid,id,name) AS( VALUES (-1,0,'root'), (0,1,'----branch1'), (1,11,'--------branch11'), (11,111,'------------branch111'), (111,1111,'----------------branch1111'), (111,1112,'----------------branch1112'), (11,112,'------------branch112'), (1,12,'--------branch12'), (0,2,'----branch2'), (2,21,'--------branch21')), temp(parentid,id,name) AS( SELECT parentid,id,name FROM test WHERE name='root' union all SELECT t1.parentid,t1.id,t1.name FROM test t1,temp t2 WHERE t1.PARENTID=t2.id ) SELECT name FROM temp
VALUES
(-1,0,'root'),
(0,1,'----branch1'),
(1,11,'--------branch11'),
(11,111,'------------branch111'),
(111,1111,'----------------branch1111'),
(111,1112,'----------------branch1112'),
(11,112,'------------branch112'),
(1,12,'--------branch12'),
(0,2,'----branch2'),
(2,21,'--------branch21')),
temp(parentid,id,name) AS(
SELECT parentid,id,name FROM test WHERE name='root'
union all
SELECT t1.parentid,t1.id,t1.name FROM test t1,temp t2 WHERE t1.PARENTID=t2.id
)
SELECT name FROM temp