sql - 如何为 sql select 生成序列号，为后代项目提供子编号？

Question

我想为 select 生成序列号，为后代项目提供子编号。
我希望数字是以下格式:

根:1...n

根的 child :1.1 -> 1.n

子 child :1.1.1 -> 1.1.n

等等...

我有一个 Item 表，它有一个 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

View on DBFiddle