postgrei如何生成序列号的sql选择，为后代项目提供子号码?

Question

我想为select生成序列号，为后代项目提供子编号。

我希望这些数字的格式如下:

• 根:1…n
• 根的子节点:1.1 ->1. n
• sub children: 1.1.1 ->1.1.n

我有一个条目表，它有一个owner_ref外键表:(项的名称只是一个例子，它可以是任何东西)

 id | item_name  | parent_id | owner_ref_id 
----|------------|-----------|--------------
 1  | item_1     | null      |  1
 2  | item_1.1   | 1         |  1
 3  | item_1.1.1 | 2         |  1
 4  | item_2     | null      |  1
 5  | item_2.1   | 4         |  1
 6  | item_2.2   | 4         |  1
--------------------------------------------

结果应该是这样的:

 seq_num | item_name  | parent_id | owner_ref_id 
---------|------------|-----------|--------------
 1       | item_1     | null      |  1
 1.1     | item_1.1   | 1         |  1
 1.1.1   | item_1.1.1 | 2         |  1
 2       | item_2     | null      |  1
 2.1     | item_2.1   | 4         |  1
 2.2     | item_2.2   | 4         |  1
--------------------------------------------

Answer 1

使用递归CTE形成树形结构-

with recursive nodes(id,item_name, parent_id,lvl, path) as (
    select id,item_name, parent_id, 1
  , row_number() OVER (order by parent_id nulls first)::text as path
    from items where parent_id is null
union all
    select o.id,o.item_name, o.parent_id,n.lvl+1, n.path || '.' || 
  row_number() OVER (partition by o.parent_id order by o.parent_id)::text
    from items o
    join nodes n on n.id = o.parent_id
)
select *
from nodes
order by id

查看DBFiddle