Select rows from table using tree order

I have table witch contains fields: id, parent_id, name (etc.)

i want to order this table in "tree travel order" ie.

id  parent_id
1,  0
3,  1
5,  1

2,  0
8,  2

4,  0
9,  4

(...)

in short describe: take root node, append all children, take next root node append children etc.

---

By your description I assume you mean breadth-first order, which could be easly done using a WITH RECURSIVE query (PostgreSQL 8.4+):

WITH RECURSIVE tree 
AS 
(
    SELECT 
        node_name, id, parent_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, parent_id, node_name FROM tree;

You could also use depth-first order using the following SQL:

WITH RECURSIVE tree 
AS 
(
    SELECT 
        node_name, id, parent_id, NULL::varchar AS parent_name, id::text AS path 
    FROM foo WHERE parent_id IS NULL 
    UNION
    SELECT 
        node_name, f1.id, f1.parent_id, 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, empno, parent_id, node_name, path FROM tree ORDER BY path;

---

As noticed by synergetic, the solution for depth-first order provided by Diogo Biazus won't work for id's with different number of digits.

But you can use this solution instead, that uses arrays of integer :

WITH RECURSIVE tree 
AS 
(
    SELECT 
        node_name, id, parent_id, NULL::varchar AS parent_name, array[id] AS path 
    FROM foo WHERE parent_id IS NULL 
    UNION
    SELECT 
        node_name, f1.id, f1.parent_id, tree.node_name AS parent_name, tree.path || f1.id AS path 
    FROM 
        tree 
        JOIN foo f1 ON f1.parent_id = tree.id
) 
SELECT node_name, empno, parent_id, node_name, path FROM tree ORDER BY path;

---

您也可以使用优秀的LTree模块，但您需要稍微重新组织数据。

链接地址: http://www.djcxy.com/p/30464.html

上一篇: 高效删除树形数据结构

下一篇: 使用树形顺序从表中选择行