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.