recursivas - cursor recursivo sql server

¿Es posible hacer una consulta SQL recursiva?

Question

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!

Answer 1

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.

Answer 2

Creo que es más fácil en SQL 2008 con HierarchyID

Answer 3

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.

Answer 4

Desde la versión 8.4, PostgreSQL tiene soporte de consulta recursivo para expresiones de tabla comunes usando la sintaxis SQL WITH SIN.

Answer 5

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

Answer 6

Hay algunas maneras de hacer lo que necesita en PostgreSQL.

• Si puede instalar módulos, mire el contrib de tablefunc. Tiene una función connectby () que maneja los árboles que atraviesan. http://www.postgresql.org/docs/8.3/interactive/tablefunc.html

• También consulte la contribución de ltree, que puede adaptar a su tabla para usar: http://www.postgresql.org/docs/8.3/interactive/ltree.html

• O puede recorrer el árbol usted mismo con una función PL / PGSQL.

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

Answer 7

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;

Answer 8

Oracle tiene "START WITH" y "CONNECT BY"

select 
    lpad('' '',2*(level-1)) || to_char(child) s

from 
    test_connect_by 

start with parent is null
connect by prior child = parent;

http://www.adp-gmbh.ch/ora/sql/connect_by.html

Answer 9

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.

Answer 10

Si está usando SQL Server 2005, hay una manera realmente genial de hacer esto usando Common Table Expressions.

Elimina todo el trabajo de crear una tabla temporal, y básicamente te permite hacerlo todo con solo un CON y UNIÓN.

Aquí hay un buen tutorial:

http://searchwindevelopment.techtarget.com/tip/0,289483,sid8_gci1278207,00.html

Answer 11

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.

Answer 12

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:

• Consultas jerárquicas en PostgreSQL

Answer 13

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.

Answer 14

usa una expresión de tabla común .

Puede querer indicar que esto es solo SQL Server 2005 o superior. Dale Ragan

aquí hay un artículo sobre recursión de SqlTeam sin expresiones de tabla comunes.