这是使用递归CTE的解决方案：

Here is a solution using recursive CTEs:

WITH RECURSIVE cte AS (
    SELECT LPAD(id::text, 3, '0') AS marker, '   ' AS buffer,
        id, parent_id, name::text
    FROM yourTable t WHERE parent_id IS NULL
    FROM yourTable t WHERE parent_id IS NULL
    UNION ALL
        SELECT t2.marker || ':' || LPAD(t1.parent_id::text, 3, '0') || ':' ||
            LPAD(t1.id::text, 3, '0') AS marker,
            t2.buffer || '   ', t1.id, t1.parent_id, t2.buffer || t1.name
    FROM yourTable t1
    INNER JOIN cte t2
        ON t1.parent_id = t2.id
)

SELECT name FROM cte ORDER BY marker;

演示

这里的基本思想是构建路径字符串，该字符串跟踪从每个节点返回其根的完整路径（根由其 parent_id 的节点给出是 NULL ）。然后，我们只需在此路径上执行一个 ORDER BY 即可生成您想要的订单。

The basic idea here is to build path strings which track the complete path from every node going to back its root (the root being given by a node whose parent_id is NULL). Then, we simply do a single ORDER BY on this path to generate the order you want.