根据你所要做的事情，你无疑可以改进这一点。例如，也许你想把路径作为一个数组。
请注意，在从sqlite3到IBM db2的大多数现代数据库中也可以使用非常类似的方法-请参阅您的本地文档。
比如：

;
CREATE TABLE nodes (
  "id" int primary key,
  parent_id int,
  name text,
  -- other fields go here
  unique(parent_id, id) -- creates an index
 );
 
 
 CREATE VIEW hierarchy_view AS
 WITH RECURSIVE hierarchy AS (
  -- Handle case where no parent.
  SELECT 
    id, parent_id, id root_id, name, 
    name path,
    ARRAY[id]::int[] id_path,
    0::int depth
  FROM nodes t
  WHERE parent_id is null
  UNION ALL
  -- Handle case where there is a parent.
  -- In this case the "path" is the parent_path and name, 
  -- concatenated with ' / ' as a separator.
  SELECT 
    child.id, child.parent_id, parent.root_id, child.name, 
    parent.path || (' / ' || child.name) path,
    parent.id_path || child.id id_path,
    parent.depth + 1 depth
  FROM nodes child
  INNER JOIN hierarchy parent
    ON parent.id = child.parent_id
)
SELECT * 
FROM hierarchy h;

现在，让我们填充一些值：

insert into nodes(id, parent_id, name) 
 values
 (1, null, 'foo'),
 (2, 1, 'bar'),
 (3, 1, 'baz'),
 (4, 2, 'quux'),
 (5, 4, 'ftang')
 ;

要查询：

-- Get path for a node
SELECT path FROM hierarchy_view WHERE id = :id

-- Get whole tree for a node, sorted by path
SELECT * FROM hierarchy_view WHERE parent_id = :parentId
ORDER BY path ASC, id_path ASC

您至少需要id和parent_id上的索引。

您可以使用带有concat的recursive查询来完成此操作，只需在externwhere子句中传递id以获取路径

WITH RECURSIVE cte(id, path) AS (
  SELECT id, t.name
  FROM mytable t
  WHERE parent_id is null
  UNION ALL
  SELECT t.id, concat(path,'/', t.name)
  FROM mytable t
  INNER JOIN cte c ON c.id = t.parent_id
)
SELECT * 
FROM cte
WHERE id = 5;

Demo here

下面的查询由一个函数组成，该函数包含用于生成路径的递归cte。然后可以根据示例调用该函数，以在脚本的其他位置生成路径：

create or replace function getPath(node_id int) returns text as $$
   with recursive cte(id, p_id, v) as (
      select t.* from tbl t where t.parent_id is null
      union all
      select t.id, t.parent_id, c.v||'/'||t.name from cte c 
      join tbl t on  t.parent_id = c.id
   )
   select min(c.v) from cte c where c.id = node_id
$$
language sql

用法：

select getPath(5) -- id1/id2/abc

See fiddle

如果您正在检索另一个结果集中返回的每个id的路径，则直接联接到递归CTE会更有意义，该CTE是以层次结构的根节点为种子的。
当为一个特定的节点获取/构建路径时，从根节点开始构建整个树是没有意义的，我们应该从指定的节点开始，沿着树向上走：

WITH RECURSIVE cte (id, parent_id, path) AS (

    SELECT id, parent_id, name
    FROM tbl
    WHERE id = 5 -- the id of the node for which we need to build the path

    UNION ALL

    SELECT t.id, t.parent_id, CONCAT_WS(' / ', t.name, path)
    FROM tbl t
    JOIN cte c ON t.id = c.parent_id

)
SELECT path
FROM cte
WHERE parent_id IS NULL;

或 Package 在函数中：

CREATE OR REPLACE FUNCTION getPath(node_id INT) RETURNS TEXT AS $$
    WITH RECURSIVE cte (id, parent_id, path) AS (
        SELECT id, parent_id, name
        FROM tbl
        WHERE id = node_id
        UNION ALL
        SELECT t.id, t.parent_id, CONCAT_WS(' / ', t.name, path)
        FROM tbl t
        JOIN cte c ON t.id = c.parent_id
    )
    SELECT path FROM cte WHERE parent_id IS NULL
$$
LANGUAGE SQL

用法：

SELECT getPath(5); -- returns "id1 / id2 / abc"

这是一个db<>fiddle