我有表女巫包含字段：id,parent_id,name(等)
我想在“树旅行顺序”中订购此表,即.
id parent_id 1,0 3,1 5,1 2,0 8,2 4,0 9,4
(……)
总之描述：取根节点,追加所有子节点,取下一个根节点追加子节点等.
解决方法
根据您的描述,我假设您的意思是广度优先顺序,可以使用WITH RECURSIVE查询(Postgresql 8.4)轻松完成：
WITH RECURSIVE tree
AS
(
SELECT
node_name,id,NULL::varchar AS parent_name
FROM foo
WHERE parent_id IS NULL
UNION
SELECT
node_name,f1.id,f1.parent_id,tree.node_name AS parent_name
FROM
tree
JOIN foo f1 ON f1.parent_id = tree.id
)
SELECT node_name,empno,node_name FROM tree;
您还可以使用以下sql使用深度优先顺序：
WITH RECURSIVE tree
AS
(
SELECT
node_name,NULL::varchar AS parent_name,id::text AS path
FROM foo WHERE parent_id IS NULL
UNION
SELECT
node_name,tree.node_name AS parent_name,tree.path || '-' || f1.id::text AS path
FROM
tree
JOIN foo f1 ON f1.parent_id = tree.id
)
SELECT node_name,node_name,path FROM tree ORDER BY path;
