使用树顺序从表中选择行

根据您的描述,我假设您指的是宽度优先顺序,使用WITH RECURSIVE查询(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;

还可以使用以下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;

协同的 双叶薯蓣 不适用于不同位数的id。

但您可以使用此解决方案,它使用整数数组:

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

SELECT * FROM table ORDER BY id,parent_id

它应该按照在查询中放置的顺序对我的列进行排序。

SELECT * FROM table ORDER BY id GROUP BY parent_id

我也建议你阅读这篇文章: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/