recursivas - SQL Encuentra todos los descendientes directos en un árbol

como hacer un arbol en sql

Question

Tengo un árbol en mi base de datos que se almacena usando enlaces de id.

Una muestra de lo que tengo para los datos en la tabla es:

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

Ahora me gustaría obtener una lista de todos los descendientes directos de un nodo determinado, pero si no existe ninguno, me gustaría que devuelva el nodo.

Quiero que el retorno para la consulta para hijos de id = 3 sea:

children -------- 4 5

Entonces la consulta para los hijos de id = 4 es:

children -------- 4

Puedo cambiar la forma en que estoy almacenando el árbol en un conjunto anidado, pero no veo cómo eso haría posible la consulta que deseo.

Answer 1

Encontré una consulta que funciona de la manera que quería.

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 ) )

Answer 2

En el nuevo PostgreSQL 8.4 , puede hacerlo con un 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

Vea este artículo en mi blog para más detalles:

• PostgreSQL 8.4 : preservando el orden para consultas jerárquicas

En 8.3 o anterior, tendrás que escribir una función:

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'';

y seleccione de esta función:

SELECT  *
FROM    fn_hierarchy_connect_by(4, 1)

El primer parámetro es el id la raíz, el segundo debe ser 1 .

Vea este artículo en mi blog para más detalles:

• Consultas jerárquicas en PostgreSQL

Actualizar:

Para mostrar solo los niños de primer nivel o el nodo si los niños no existen, emita esta consulta:

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
        )

Esto es más eficiente que un JOIN , ya que la segunda consulta tomará como máximo dos escaneos de índice: el primero para asegurarse de averiguar si existe un niño, el segundo para seleccionar la fila principal si no existen niños.