T-SQL, Wie man alle Elemente eines Baumes in T-SQL?

Ich habe ein problem mit einem t-sql-Abfrage.

Sagen wir, ich habe ein Kategorien-Baum (Kategorien ID)

cat_table

 1
 |
 2-\
 |  3-\
 6  |  5
 |  4  |
...   ...

ads_table

ad_ID
category_ID

natürlich die category_ID Spalte Verweise auf die ID Spalte in der cat_table

das problem ist, wie man (rekursiv ?) alle anzeigen von alle Kategorien, die oben-die meisten Eltern ist die 1. Kategorie?

InformationsquelleAutor Tony | 2010-11-24
  1. 1

    Sind Sie vertraut mit Common Table Expressions in SQL Server? Eine der vielen einen CTE hat, ist zu tun, rekursive Abfragen.

    Folgende ist einer der besten Artikel, die ich gefunden habe zum Thema:

    http://www.4guysfromrolla.com/webtech/071906-1.shtml

    • Artikel-link sagt: Die Seite kann nicht angezeigt werden, da ein interner server Fehler aufgetreten.
    • Beantworten "Links zu externen Ressourcen sind erwünscht, aber bitte fügen Sie Rahmen um den link, damit Ihre Kolleginnen und Nutzer haben eine Vorstellung davon, was es ist und warum es da ist. Immer zitieren die meisten relevanten Teil eine wichtige Verbindung, im Falle der Ziel-Website nicht erreichbar ist oder geht endgültig offline."
    InformationsquelleAutor Randy Minder
  2. 9

    versuchen Sie, eine rekursive Common Table Expressions, aka "CTE" (verfügbar in SQL Server 2005 und höher) wie diese:

    --go through a nested table supervisor - user table and display the chain
DECLARE @Contacts table (id varchar(6), first_name varchar(10), reports_to_id varchar(6))
INSERT @Contacts VALUES ('1','Jerome', NULL )  -- tree is as follows:
INSERT @Contacts VALUES ('2','Joe'   ,'1')     --                      1-Jerome
INSERT @Contacts VALUES ('3','Paul'  ,'2')     --                     /       \
INSERT @Contacts VALUES ('4','Jack'  ,'3')     --              2-Joe           9-Bill
INSERT @Contacts VALUES ('5','Daniel','3')     --            /      \              \
INSERT @Contacts VALUES ('6','David' ,'2')     --     3-Paul          6-David       10-Sam
INSERT @Contacts VALUES ('7','Ian'   ,'6')     --    /     \            /   \
INSERT @Contacts VALUES ('8','Helen' ,'6')     -- 4-Jack  5-Daniel   7-Ian    8-Helen
INSERT @Contacts VALUES ('9','Bill ' ,'1')     --
INSERT @Contacts VALUES ('10','Sam'  ,'9')     --

DECLARE @Root_id  char(4)

--get 2 and below
SET @Root_id=2
PRINT '@Root_id='+COALESCE(''''+@Root_id+'''','null')
;WITH StaffTree AS
(
    SELECT 
        c.id, c.first_name, c.reports_to_id, c.reports_to_id as Manager_id, cc.first_name AS Manager_first_name, 1 AS LevelOf
        FROM @Contacts                  c
            LEFT OUTER JOIN @Contacts  cc ON c.reports_to_id=cc.id
        WHERE c.id=@Root_id OR (@Root_id IS NULL AND c.reports_to_id IS NULL)
    UNION ALL
        SELECT 
            s.id, s.first_name, s.reports_to_id, t.id, t.first_name, t.LevelOf+1
        FROM StaffTree            t
            INNER JOIN @Contacts  s ON t.id=s.reports_to_id
    WHERE s.reports_to_id=@Root_id OR @Root_id IS NULL OR t.LevelOf>1
)
SELECT * FROM StaffTree

    Ausgabe:

    @Root_id='2   '
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
2      Joe        1             1          Jerome             1
3      Paul       2             2          Joe                2
6      David      2             2          Joe                2
7      Ian        6             6          David              3
8      Helen      6             6          David              3
4      Jack       3             3          Paul               3
5      Daniel     3             3          Paul               3

(7 row(s) affected)

    ändern @Root_id um andere Ausgabe:

    @Root_id=null
id     first_name reports_to_id Manager_id Manager_first_name LevelOf
------ ---------- ------------- ---------- ------------------ -----------
1      Jerome     NULL          NULL       NULL               1
2      Joe        1             1          Jerome             2
9      Bill       1             1          Jerome             2
10     Sam        9             9          Bill               3
3      Paul       2             2          Joe                3
6      David      2             2          Joe                3
7      Ian        6             6          David              4
8      Helen      6             6          David              4
4      Jack       3             3          Paul               4
5      Daniel     3             3          Paul               4

(10 row(s) affected)
    InformationsquelleAutor KM.
  3. 2

    Gibt es eine option, um zu vermeiden, recurrency in Baum-browsing-Abfragen. Sie können hinzufügen 'Pfad' - Spalte, um Ihre Kategorien-Baum. Es sollte enthalten jedes element Vorfahren IDs getrennt mit einigen nicht-numerisches Zeichen (wie slash).

    Beispielsweise Ihre "ID=4" Kategorie-Pfad würde dann so Aussehen: "/1/2/3/"
    Wenn Sie jetzt beitreten, werden Ihre anzeigen Tabelle zu Kategorien, die Sie tun müssen, folgende: 

    select * from ads_table 
inner join cat_table on cat_table.ID = ads_table.category_ID
where cat_table.Path like '/1/%'

    Und das ist Ihre Abfrage.

    Lesen Sie mehr zu diesem Thema auf meiner blog-post

    • Ich würde sicherlich sagen, das ist mehr bevorzugt und gut mit der Leistung..
    • Was ist, wenn ein Kind hat zwei Eltern?
    • dies ist hier nicht der Fall und würde eine neue Art von Einstellung bis die Tabelle(wo der Verweis auf den 2. Elternteil?) Es könnte sich lohnen, steckte es in eine neue Frage hier auf SO. an Alle: Diese Lösung ist sehr gut für kleine oder fast statische Bäume. Es erfordert ein wenig mehr Speicher. Bei der Umstrukturierung(durch hinzufügen, verschieben, entfernen, teilweise Bäume) müssen Sie möglicherweise aktualisieren Sie den Pfad der alle Knoten beteiligt und alle Indizes(falls vorhanden) dieser Spalte. Aber Sie brauchen nur eine einfache Abfrage, um die gesamte Hierarchie.
    InformationsquelleAutor mkarczewski
Schreibe einen Kommentar