Abfrage zum abrufen von übergeordneten Datensätzen mit untergeordneten Datensatz, gefolgt von den nächsten Eltern-Kind-Datensätzen in mysql

Die Lösung, die ich vorschlagen, hier greift das Konzept der materialized path. Der folgende Code ist ein Beispiel der materialisierte Pfade unter Verwendung von Beispieldaten. Ich hoffe, es hilft dir zu verstehen, materialized path-Konzept:

+----+--------------------------+----------+------------------+
| ID |           Name           | ParentID | MaterializedPath |
+----+--------------------------+----------+------------------+
|  1 | Parent 1                 |        0 | 1                |
|  2 | Parent 2                 |        0 | 2                |
|  4 | Parent 2 Child 1         |        2 | 2.4              |
|  6 | Parent 2 Child 1 Child 1 |        4 | 2.4.6            |
|  7 | Parent 2 Child 1 Child 2 |        4 | 2.4.7            |
|  3 | Parent 1 Child 1         |        1 | 1.3              |
|  5 | Parent 1 Child 1 Child   |        3 | 1.3.5            |
+----+--------------------------+----------+------------------+

Jeder Knoten N hat eine materialisierte Pfad dieser Pfad gibt den Weg zu gehen vom root-Knoten zum Knoten N. Es kann sein, bauen die Verkettung der Knoten-id ist. Zum Beispiel, zu erreichen, um Knoten 5 ausgehend von der Wurzel die Knoten, die Sie besuchen, Knoten 1 Knoten 3 und Knoten 5, also Knoten 5 materialized path 1.3.5

Zufällig, die Reihenfolge, die Sie suchen erreicht werden kann die Bestellung von der materialized path.

Auf dem vorherigen Beispiel, materialisierte Pfade sind buit Verkettung von strings, aber ich bevorzuge binäre Verkettung für eine Reihe von Gründen.

Bauen die materialisierte Pfade müssen Sie die folgenden rekursiven CTE:

CREATE TABLE Tree
(
    ID int NOT NULL CONSTRAINT PK_Tree PRIMARY KEY, 
    Name nvarchar(250) NOT NULL,
    ParentID int NOT NULL,
)

INSERT INTO Tree(ID, Name, ParentID) VALUES
(1, 'Parent 1', 0),
(2, 'Parent 2', 0),
(3, 'Parent 1 Child 1', 1),
(4, 'Parent 2 Child 1', 2),
(5, 'Parent 1 Child 1 Child', 3),
(6, 'Parent 2 Child 1 Child 1', 4),
(7, 'Parent 2 Child 1 Child 2', 4)

GO
WITH T AS
(
    SELECT
        N.ID, N.Name, N.ParentID, CAST(N.ID AS varbinary(512)) AS MaterializedPath
    FROM
        Tree N
    WHERE
        N.ParentID = 0

    UNION ALL

    SELECT
        N.ID, N.Name, N.ParentID, CAST( T.MaterializedPath + CAST(N.ID AS binary(4)) AS varbinary(512) ) AS MaterializedPath
    FROM
        Tree N INNER JOIN T
            ON N.ParentID = T.ID

)
SELECT *
FROM T
ORDER BY T.MaterializedPath

Ergebnis:

+----+--------------------------+----------+----------------------------+
| ID |           Name           | ParentID |      MaterializedPath      |
+----+--------------------------+----------+----------------------------+
|  1 | Parent 1                 |        0 | 0x00000001                 |
|  3 | Parent 1 Child 1         |        1 | 0x0000000100000003         |
|  5 | Parent 1 Child 1 Child   |        3 | 0x000000010000000300000005 |
|  2 | Parent 2                 |        0 | 0x00000002                 |
|  4 | Parent 2 Child 1         |        2 | 0x0000000200000004         |
|  6 | Parent 2 Child 1 Child 1 |        4 | 0x000000020000000400000006 |
|  7 | Parent 2 Child 1 Child 2 |        4 | 0x000000020000000400000007 |
+----+--------------------------+----------+----------------------------+

Den oben genannten rekursiven CTE beginnt mit dem root-Knoten. Die Berechnung der materialized path für root-Knoten ist trivial einfach, es ist die ID des Knotens selbst. Auf der nächsten iteration der CTE schließt root-nodes mit child nodes. Die materialisierte Weg für ein Kind-Knoten CN ist die Verkettung der materialisierten Pfad des übergeordneten Knotens PN und der id des Knotens CN. Nachfolgende Iterationen Voraus eine Ebene nach unten auf dem Baum, bis die Blattknoten erreicht sind.