tabla recursivo recursivas recursiva jerarquicas ejemplos crear consultas consulta sql postgresql recursive-query

recursivas - cursor recursivo sql server



¿Es posible hacer una consulta SQL recursiva? (14)

Tengo una mesa similar a esto:

CREATE TABLE example ( id integer primary key, name char(200), parentid integer, value integer);

Puedo usar el campo parentid para organizar los datos en una estructura de árbol.

Ahora, aquí está lo poco que no puedo resolver. Dado un parentid, ¿es posible escribir una instrucción SQL para sumar todos los campos de valor debajo de parentid y recurse en la rama del árbol?

ACTUALIZACIÓN: Estoy usando posgreSQL para que las sofisticadas características de MS-SQL no estén disponibles para mí. En cualquier caso, me gustaría que esto sea tratado como una pregunta genérica de SQL.

Por cierto, estoy muy impresionado de tener 6 respuestas dentro de los 15 minutos de hacer la pregunta! Ir desbordamiento de pila!

Aquí hay un script de ejemplo que usa la expresión de tabla común:

with recursive sumthis(id, val) as ( select id, value from example where id = :selectedid union all select C.id, C.value from sumthis P inner join example C on P.id = C.parentid ) select sum(val) from sumthis

El script anterior crea una tabla ''virtual'' llamada sumthis que tiene columnas id y val . Se define como el resultado de dos selecciones fusionadas con union all .

Primero select obtiene la raíz ( where id = :selectedid ).

La segunda select sigue iterativamente a los hijos de los resultados previos hasta que no haya nada que devolver.

El resultado final se puede procesar como una tabla normal. En este caso, la columna val se suma.

Del mismo modo que un breve aparte, aunque la pregunta ha sido respondida muy bien, debe tenerse en cuenta que si tratamos esto como a:

pregunta SQL genérica

entonces la implementación de SQL es bastante sencilla, ya que SQL''99 permite la recursión lineal en la especificación (aunque creo que ningún RDBMS implementa completamente el estándar) a través de la declaración WITH RECURSIVE . Entonces, desde una perspectiva teórica, podemos hacer esto ahora mismo.

El siguiente código se compila y se prueba OK.

create or replace function subtree (bigint) returns setof example as $$ declare results record; entry record; recs record; begin select into results * from example where parent = $1; if found then for entry in select child from example where parent = $1 and child parent loop for recs in select * from subtree(entry.child) loop return next recs; end loop; end loop; end if; return next results; end; $$ language ''plpgsql'';

En mi caso, se necesita la condición "hijo <> padre" porque los nodos apuntan a sí mismos.

Que te diviertas :)

Hay algunas maneras de hacer lo que necesita en PostgreSQL.

Algo como esto:

create or replace function example_subtree (integer) returns setof example as ''declare results record; child record; begin select into results * from example where parent_id = $1; if found then return next results; for child in select id from example where parent_id = $1 loop for temp in select * from example_subtree(child.id) loop return next temp; end loop; end loop; end if; return null; end;'' language ''plpgsql''; select sum(value) as value_sum from example_subtree(1234);

Ninguno de los ejemplos funcionó bien para mí, así que lo solucioné así:

declare results record; entry record; recs record; begin for results in select * from project where pid = $1 loop return next results; for recs in select * from project_subtree(results.id) loop return next recs; end loop; end loop; return; end;

Si desea una solución portátil que funcione en cualquier RDBMS ANSI SQL-92 , deberá agregar una nueva columna a su tabla.

Joe Celko es el autor original del enfoque de conjuntos anidados para almacenar jerarquías en SQL. Puede buscar en Google la jerarquía de "conjuntos anidados" para comprender más sobre el fondo.

O simplemente puede cambiar el nombre de parentid a leftid y agregar un derecho .

Aquí está mi intento de resumir Conjuntos anidados, que serán lamentablemente cortos porque no soy Joe Celko: SQL es un lenguaje basado en conjuntos, y el modelo de adyacencia (que almacena el ID padre) NO es una representación de una jerarquía basada en conjuntos. Por lo tanto, no existe un método puro basado en conjuntos para consultar un esquema de adyacencia.

Sin embargo , la mayoría de las principales plataformas han introducido extensiones en los últimos años para hacer frente a este problema preciso. Entonces, si alguien responde con una solución específica de Postgres, utilícela por todos los medios.

Si necesita almacenar gráficos arbitrarios, no solo jerarquías, podría empujar a Postgres hacia un lado y probar una base de datos de gráficos como AllegroGraph :

Todo en la base de datos de gráficos se almacena como un nodo triple (nodo de origen, borde, destino) y le ofrece soporte de primera clase para manipular la estructura del gráfico y consultarlo usando un lenguaje similar a SQL.

No se integra bien con algo como Hibernate o Django ORM, pero si te tomas en serio las estructuras de los gráficos (no solo las jerarquías como el modelo de Conjunto anidado), échale un vistazo.

También creo que Oracle finalmente ha agregado un soporte para gráficos reales en sus últimos productos, pero estoy sorprendido de que haya tardado tanto, muchos problemas podrían beneficiarse de este modelo.

Una forma estándar de hacer una consulta recursiva en SQL es CTE recursivo. PostgreSQL admite desde 8.4 .

En versiones anteriores, puede escribir una función recursiva de devolución de conjuntos:

CREATE FUNCTION fn_hierarchy (parent INT) RETURNS SETOF example AS $$ SELECT example FROM example WHERE id = $1 UNION ALL SELECT fn_hierarchy(id) FROM example WHERE parentid = $1 $$ LANGUAGE ''sql''; SELECT * FROM fn_hierarchy(1)

Ver este artículo:

es este servidor SQL? ¿No podría escribir un procedimiento almacenado TSQL que recorre y une los resultados juntos?

También estoy interesado si hay una forma SQL de hacer esto. De los bits que recuerdo de mi clase de bases de datos geográficos, debería haberlos.