sql - 排序树数据结构

Question

我有一个这样的表:

CREATE TABLE tree ( 
 id integer NOT NULL,
 name character varying(50) NOT NULL,
 parentid integer,
 displayorder integer NOT NULL,
 CONSTRAINT tree_id PRIMARY KEY (id)
) 

The displayorders are relative in the same parent.

我坚持对这些数据进行排序以获得如下输出:

1 -> 1.1 -> 1.1.1 -> 1.1.2 -> 1.1.3 -> 1.2 -> 1.3 -> 2 -> 3

如果你能帮助我，我将不胜感激。谢谢!

Answer 1

您需要一个递归查询来遍历树。要在每个级别上正确应用 displayorder，您还需要收集每个节点的路径，以便进行排序:

with recursive all_nodes as (
   select id, name, parentid, displayorder, array[id] as path
   from tree
   where parentid is null
   union all
   select c.id, c.name, c.parentid, c.displayorder, p.path||c.id
   from tree c 
     join all_nodes p on c.parentid = p.id
)
select id, name
from all_nodes
order by path, displayorder;

在线示例:http://rextester.com/MJEL66144