Immer zählt/Summen auf jeder Ebene einer hierarchischen Abfrage mit CONNECT BY

Ich habe einen Teufel von einer Zeit mit diesem. Ich bin beim schreiben einer Abfrage (Oracle), davor ein Tisch mit einer rekursiven Beziehung (hierarchische) und die Gesamtzahl der gespeicherten Datensätze in einer anderen Tabelle an und unter jedem Knoten im Baum. Die anderen Tabellen nur die Datensätze im Zusammenhang mit der Blatt-Knoten. Jedoch möchte ich, um Summen über und unter jedem Knoten im Baum. Zum Beispiel, sagen wir ich habe zwei Tabellen. VERZEICHNISSE das Verzeichnis enthält Namen und eine rekursive Beziehung, die Identifikation der Struktur der Verzeichnisse und DATEIEN enthält Datei-Informationen, die mit einem Fremdschlüssel zur DIRS, der angibt, das Verzeichnis, die Datei befindet sich in:

DIRS
====
DIR_ID 
PARENT_DIR_ID
DIR_NAME

FILES
=====
FILE_ID
FILE_NAME
DIR_ID
FILE_SIZE

Wenn DIRS enthält:

DIR_ID   PARENT_DIR_ID   DIR_NAME
======   =============   ========
1                        ROOT
2        1               DIR1_1
3        1               DIR1_2
4        2               DIR2_1
5        2               DIR2_2

und DATEIEN enthält

FILE_ID   FILE_NAME   DIR_ID   FILE_SIZE
=======   =========   ======   =========
1         test1.txt   5        100
2         test2.txt   5        200
3         test5.txt   5         50 
4         test3.txt   3        300
5         test4.txt   3        300
6         test6.txt   4        100

Möchte ich eine Abfrage, wird der Pfad, zusammen mit der Anzahl von Dateien in oder unterhalb jeder Knoten in der Hierarchie. Im Grunde ein rollup von der Anzahl der Dateien. So das Ergebnis der Abfrage würde folgendermaßen Aussehen:

Path                    File_Count
=====                   ===========
/ROOT                   6
/ROOT/DIR1_1            4
/ROOT/DIR1_1/DIR2_1     1
/ROOT/DIR1_1/DIR2_2     3
/ROOT/DIR1_2            2

UPDATE SQL-Skript zum erstellen der Tabellen mit Beispiel-Daten entsprechen den oben:

create table DIRS (dir_id number(38) primary key
    , parent_dir_id number(38) null references DIRS(dir_id)
    , dir_name varchar2(128) not null);

create table FILES (file_id number(38) primary key
    , file_name varchar2(128) not null
    , dir_id number(38) not null references DIRS(dir_id)
    , file_size number not null
    , unique (dir_id, file_name));

insert into DIRS 
select 1, null, 'ROOT' from dual
union all select 2, 1, 'DIR1_1' from dual 
union all select 3, 1, 'DIR1_2' from dual 
union all select 4, 2, 'DIR2_1' from dual 
union all select 5, 2, 'DIR2_2' from dual;

insert into files
select 1, 'test1.txt', 5, 100 from dual
union all select 2, 'test2.txt', 5, 200 from dual
union all select 3, 'test5.txt', 5, 50 from dual
union all select 4, 'test3.txt', 3, 300 from dual
union all select 5, 'test4.txt', 3, 300 from dual
union all select 6, 'test6.txt', 4, 100 from dual;

commit;
  • Welche version von Oracle? 11g R2-add recursive sub-query factoring, die vielleicht eine klarere Lösung, als connect by.
  • Ja 11g R2. Blick auf die "sub-query factoring". Nicht vertraut mit dieser.
  • Sieht aus wie die sub-query factoring wurde Hinzugefügt, Oracle 9.2
  • Ich sehe nicht, wie das sub-query factoring würde mir hier helfen. Das problem ist der Einstieg zählt, basierend auf einer Tabelle mit einer rekursiven Beziehung und erste summensatz zählt in jedem Knoten. Ich bin mir nicht klar, wie das helfen würde.
  • Ich denke, dass ich es herausgefunden. Es ist nicht schön und ich habe die sub-query factoring zur Vereinfachung der Abfrage. Es muss aber ein eleganter Weg, dies zu tun.
  • Sub-query factoring zunächst nicht unterstützt Rekursion. Aber als der 11g R2 (11.2.0.1) "Die subquery_factoring_clause jetzt unterstützt rekursive subquery factoring (rekursiv), die ermöglicht die Abfrage von hierarchischen Daten. Diese Funktion ist mächtiger als die VERBINDUNG, dass es in der Tiefe-first-search und breadth-first-Suche, und unterstützt mehrere rekursiven Zweige. Eine neue search_clause und cycle_clause lassen, geben Sie eine Bestellung für die Zeilen und markieren Sie die Zyklen in der Rekursion." docs.oracle.com/cd/E11882_01/server.112/e26088/...
  • GregH, könnten Sie fügen Sie Ihre Lösung als Antwort? Ich würde gerne vergleichen mit unseren Lösungen.

InformationsquelleAutor GregH | 2012-09-10
Schreibe einen Kommentar