SQL 查找树中的所有直系子孙[英] SQL Find all direct descendants in a tree

本文是小编为大家收集整理的关于SQL 查找树中的所有直系子孙的处理方法,想解了SQL 查找树中的所有直系子孙的问题怎么解决?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

详情请看我博客中的这篇文章:

在 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.

更多详情请参阅我博客中的这篇文章:

更新:

要仅显示第一级子节点,如果子节点不存在,则显示节点本身,请发出以下查询:

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 更有效,因为第二个查询最多只进行两次索引扫描:第一个确保找出是否存在子行,第二个查询如果不存在则选择父行有孩子.

本文地址:https://www.itbaoku.cn/post/1764139.html