sql - SQL查找树的所有直系后裔

我的数据库中有一棵树，它使用父id链接存储。
表中数据的示例如下：
id |名称|父id
---+-------------+-----------
0 |根|空
1 |节点1 | 0
2 |节点2 | 0
3 |节点1.1 | 1
4 |节点1.1.1 | 3
5 |节点1.1.2 | 3
现在我想得到一个给定节点的所有直接子孙的列表，但是如果没有，我希望它只返回节点本身。
我希望id=3的子项的查询返回：
儿童
--------
四
五
则id=4的子项的查询为：
儿童
--------
四
我可以更改将树存储为嵌套集的方式，但我不知道这将如何使我想要的查询成为可能。

最佳答案

在newPostgreSQL 8.4中，您可以使用一个CTE：

WITH RECURSIVE q AS
        (
        SELECT  h, 1 AS level, ARRAY[id] AS breadcrumb
        FROM    t_hierarchy h
        WHERE   parent = 0
        UNION ALL
        SELECT  hi, q.level + 1 AS level, breadcrumb || id
        FROM    q
        JOIN    t_hierarchy hi
        ON      hi.parent = (q.h).id
        )
SELECT  REPEAT('  ', level) || (q.h).id,
        (q.h).parent,
        (q.h).value,
        level,
        breadcrumb::VARCHAR AS path
FROM    q
ORDER BY
        breadcrumb

有关详细信息，请参阅我的博客中的这篇文章：
PostgreSQL 8.4: preserving order for hierarchical query
在8.3或更早版本中，您必须编写一个函数：

CREATE TYPE tp_hierarchy AS (node t_hierarchy, level INT);

CREATE OR REPLACE FUNCTION fn_hierarchy_connect_by(INT, INT)
RETURNS SETOF tp_hierarchy
AS
$$
        SELECT  CASE
                WHEN node = 1 THEN
                        (t_hierarchy, $2)::tp_hierarchy
                ELSE
                        fn_hierarchy_connect_by((q.t_hierarchy).id, $2 + 1)
                END
        FROM    (
                SELECT  t_hierarchy, node
                FROM    (
                        SELECT  1 AS node
                        UNION ALL
                        SELECT  2
                        ) nodes,
                        t_hierarchy
                WHERE   parent = $1
                ORDER BY
                        id, node
                ) q;
$$
LANGUAGE 'sql';

并从此函数中选择：

SELECT  *
FROM    fn_hierarchy_connect_by(4, 1)

第一个参数是根id，第二个参数应该是1。
有关更多详细信息，请参阅我的博客中的这篇文章：
Hierarchical queries in PostgreSQL
更新：
若只显示第一级子级或节点本身，如果子节点不存在，则发出此查询：

SELECT  *
FROM    t_hierarchy
WHERE   parent = @start
UNION ALL
SELECT  *
FROM    t_hierarchy
WHERE   id = @start
        AND NOT EXISTS
        (
        SELECT  NULL
        FROM    t_hierarchy
        WHERE   parent = @start
        )

这比JOIN更有效，因为第二个查询最多只接受两个索引扫描：第一个查询以确定是否存在一个子，第二个则在没有子存在的情况下选择父行。