假设我有这张表:
CREATE TABLE nodes (node_path ltree);
INSERT INTO nodes VALUES ('Top.Science');
INSERT INTO nodes VALUES ('Top.Science.Astronomy.Astrophysics');
INSERT INTO nodes VALUES ('Top.Science.Astronomy.Cosmology');
INSERT INTO nodes VALUES ('Top.Hobbies');
INSERT INTO nodes VALUES ('Top.Hobbies.Amateurs_Astronomy');
INSERT INTO nodes VALUES ('Top.Collections.Pictures.Astronomy');
INSERT INTO nodes VALUES ('Top.Collections.Pictures.Astronomy.Stars');
INSERT INTO nodes VALUES ('Top.Collections.Pictures.Astronomy.Galaxies');
INSERT INTO nodes VALUES ('Top.Collections.Pictures.Astronomy.Astronauts');
INSERT INTO nodes VALUES ('Top.Dislikes');
CREATE INDEX ON nodes USING GIST (node_path);
CREATE INDEX ON nodes USING BTREE (node_path);
请注意,此表中缺少以下路径:
'Top'
'Top.Science.Astronomy'
'Top.Collections'
'Top.Collections.Pictures'
如何递归查询该表以获得树状结构(没有那些丢失的行)?
如果路径没有丢失,那么以下查询将得到结果:
with recursive
base as (
select node_path,
array[row_number() over (order by node_path)] as sort_path
from nodes
where nlevel(node_path) = 1
union all
select c.node_path,
p.sort_path||row_number() over (order by c.node_path)
from base p
join nodes c
on subpath(c.node_path, 0, -1) = p.node_path
)
select * from base order by sort_path;
但由于行缺失,它破坏了链条。结果,诸如此类的事情:
nlevel(node_path) = 1
subpath(c.node_path, 0, -1) = p.node_path
没有道理。
我如何查询这个来获取树?
请注意,我在 Stack Overflow 上发布了一个问题,我想如果我能得到上述问题的答案,那么我也许也能解决我的问题。当前的问题源于 SO 问题: