SQL在树中查找所有直接后代
我的数据库中有一棵树，使用父id链接存储。
我对表中数据的一个示例是：
id | name | parent id ---+-------------+----------- 0 | root | NULL 1 | Node 1 | 0 2 | Node 2 | 0 3 | Node 1.1 | 1 4 | Node 1.1.1| 3 5 | Node 1.1.2| 3
现在我想获得一个给定节点的所有直接后代的列表，但是如果不存在，我想让它只返回节点本身。
我希望查询id = 3的孩子的返回值为：
children -------- 4 5
然后查询id = 4的孩子是：
children -------- 4
我可以改变我将树存储到嵌套集的方式，但我不明白这会如何使我想要的查询成为可能。
在新的PostgreSQL 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 ：保留层次查询的顺序 在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 。
有关详细信息，请参阅我的博客中的此文章：
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更有效率，因为第二个查询最多只需要两次索引扫描：第一个确定是否存在子项，第二个是否存在子项时选择父行。
发现了一个以我想要的方式工作的查询。
SELECT * FROM
( SELECT id FROM t_tree WHERE name = '' ) AS i,
t_tree g
WHERE
( ( i.id = g.id ) AND
NOT EXISTS ( SELECT * FROM t_tree WHERE parentid = i.id ) ) OR
( ( i.id = g.parentid ) AND
EXISTS ( SELECT * FROM t_tree WHERE parentid = i.id ) )
链接地址: http://www.djcxy.com/p/30461.html