SQL Найти всех прямых потомков в дереве

У меня есть дерево в моей базе данных, которое хранится с использованием ссылок родительского идентификатора.

Вот пример того, что у меня есть для данных в таблице:

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 sql hierarchical-data tree
person tatsu    schedule 17.07.2009    source источник
comment
Я знаю, как это сделать с помощью курсоров или CTE, но это SQL Server.   -  person TheTXI    schedule 17.07.2009

Ответы (2)


arrow_upward
6
arrow_downward

В новом 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, поскольку второй запрос потребует не более двух сканирований индекса: первое, чтобы убедиться, что существует дочерний элемент, второе - для выбора родительской строки, если дочерние элементы отсутствуют.

person Quassnoi    schedule 17.07.2009
comment
Проблема в том, что я хотел, чтобы он отображал только дочерние элементы первого уровня, а если их не было, возвращал только сам узел. Я ответил ответом, который работает для меня после того, как я еще немного поигрался. - person tatsu; 18.07.2009
comment
+1 Отличный ответ! В настоящий момент я борюсь именно с этой проблемой и был бы признателен, если бы вы просто подтвердили, что в последней версии PostgreSQL вы все еще подходите к проблеме таким же образом. Кроме того, в идеале я ищу решение, которое будет работать как в PosgreSQL, так и в H2. - person Jack; 27.02.2013
comment
@JacobusR: первый запрос - это то, что вы должны использовать в 9.2 - person Quassnoi; 27.02.2013
comment
Спасибо, просто чтобы сообщить, что я прочитал ваш блог по этому поводу. Абсолютно здорово! Еще раз спасибо. - person Jack; 27.02.2013

arrow_upward
0
arrow_downward

Нашел запрос, который работает так, как я хотел.

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 ) )
person tatsu    schedule 17.07.2009