Czy możliwe jest wykonanie rekurencyjnego zapytania SQL?

Oto przykładowy skrypt wykorzystujący wyrażenie common table:

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

Powyższy skrypt tworzy "wirtualną" tabelę o nazwie sumthis, która zawiera kolumny id i val. Jest ona zdefiniowana jako wynik dwóch selekcji połączonych z union all.

Pierwszy select otrzymuje root (where id = :selectedid).

Druga select podąża za dziećmi poprzednich wyników iteracyjnie, dopóki nie ma nic do powrotu.

Wynik końcowy może być następnie przetwarzany jak normalna tabela. W tym przypadku kolumna val jest / align = "left" /

Od wersji 8.4, PostgreSQL obsługuje rekurencyjne zapytania dla popularnych wyrażeń tabel przy użyciu standardowej składni SQL WITH.

Jeśli chcesz przenośnego rozwiązania, które będzie działać na dowolnym ANSI SQL-92 RDBMS, musisz dodać nową kolumnę do tabeli.

Joe Celko jest oryginalnym autorem zagnieżdżone Zestawy podejście do przechowywania hierarchii w SQL. Możesz wygooglować hierarchię"zagnieżdżonych zestawów" , aby dowiedzieć się więcej o tle.

LUB możesz po prostu zmienić nazwę parentid na leftid i dodać righttid.

Oto moja próba podsumowania zagnieżdżonego Zestawy, które będą żałośnie krótkie, ponieważ nie jestem Joe Celko: SQL jest językiem opartym na zestawach, a model adjacency (przechowujący identyfikator rodzica) nie jest zestawową reprezentacją hierarchii. W związku z tym nie ma czystej metody opartej na zestawach, aby odpytywać schemat przylegania.

Jednak [8]} większość głównych platform wprowadziła rozszerzenia w ostatnich latach, aby poradzić sobie z tym konkretnym problemem. Więc jeśli ktoś odpowie z rozwiązaniem specyficznym dla Postgres, użyj go za wszelką cenę.

Standardowym sposobem wykonania rekurencyjnego zapytania w {[1] } są rekurencyjne CTE. PostgreSQL obsługuje je od 8.4.

We wcześniejszych wersjach można napisać funkcję zwracającą zbiór rekurencyjny:

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)

Zobacz ten artykuł:

• zapytania hierarchiczne w PostgreSQL

Jeśli używasz SQL Server 2005, istnieje naprawdę fajny sposób, aby to zrobić za pomocą popularnych wyrażeń tabel.

Zajmuje całą gruntową pracę z tworzeniem tymczasowej tabeli i zasadniczo pozwala zrobić to wszystko za pomocą tylko WITH I A UNION.

Oto dobry tutorial:

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

Użyj common table expression .

Może chcieć wskazać, że jest to tylko SQL Server 2005 lub wyższy. Dale Ragan

Oto Artykuł na temat rekurencji przez SqlTeam bez wspólnych wyrażeń tabel.

Poniższy kod kompiluje się i jest testowany 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';

Warunek "dziecko rodzic" jest potrzebny w moim przypadku, ponieważ węzły wskazują na siebie.

Miłej zabawy:)

Oracle ma "START WITH" I "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

Tak na marginesie chociaż pytanie zostało bardzo dobrze udzielone, należy zauważyć, że jeśli potraktujemy to jako:

Ogólne pytanie SQL

Wtedy implementacja SQL jest dość prosta, ponieważ SQL ' 99 pozwala na rekurencję liniową w specyfikacji (chociaż wierzę, że żaden rdbmss nie implementuje standardu w pełni) za pomocą instrukcji WITH RECURSIVE. Więc z teoretycznego punktu widzenia możemy to zrobić teraz.

Żaden z przykładów nie działał dla mnie dobrze więc naprawiłem to tak:

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;

Czy to SQL Server? Nie możesz napisać procedury składowanej TSQL, która przeplata i łączy wyniki razem?

Jestem również zainteresowany, czy istnieje tylko SQL sposób, aby to zrobić choć. Z bitów, które pamiętam z mojej klasy baz danych geograficznych, powinno być.

Myślę, że w SQL 2008 jest łatwiej z HierarchyID

Jeśli chcesz przechowywać dowolne wykresy, a nie tylko hierarchie, możesz przesunąć Postgres na bok i wypróbować bazę danych Wykresów, taką jak AllegroGraph :

Wszystko w bazie danych grafu jest przechowywane jako potrójne (węzeł źródłowy, krawędź, węzeł docelowy) i zapewnia obsługę pierwszej klasy do manipulowania strukturą grafu i zapytań za pomocą języka podobnego do SQL.

Nie integruje się dobrze z czymś takim jak Hibernate lub Django ORM, ale jeśli poważnie myślisz o grafie struktury (nie tylko hierarchie, jak daje Ci zagnieżdżony model zestawu) sprawdź to.

Wierzę również, że Oracle w końcu dodał obsługę prawdziwych Wykresów w swoich najnowszych produktach, ale jestem zdumiony, że trwało to tak długo, że wiele problemów może skorzystać z tego modelu.