SQL verwenden, um eine in einer Datenbank dargestellte Baumstruktur zu klonen

Question

Bei einer Tabelle, die eine hierarchische Baumstruktur darstellt und drei Spalten hat

1. ID (Primärschlüssel, nicht-autoincrementierend)
2. ParentGroupID
3. SomeValue

Ich kenne den untersten Knoten dieses Zweigs und möchte ihn in einen neuen Zweig mit der gleichen Anzahl von Elternteilen kopieren, die ebenfalls geklont werden müssen.

Ich versuche, eine einzige SQL INSERT INTO-Anweisung zu schreiben, die eine Kopie jeder Zeile, die von der gleichen Haupt ist, ist Teil eine GroupID in eine neue GroupID machen wird.

Beispiel Anfangstabelle:

ID | ParentGroupID | SomeValue
------------------------
1  |      -1       |    a
2  |       1       |    b
3  |       2       |    c

Ziel, nachdem ich eine einfache INSERT INTO-Anweisung ausgeführt habe:

ID | ParentGroupID | SomeValue
------------------------
1  |      -1       |    a
2  |       1       |    b
3  |       2       |    c
4  |      -1       |    a-cloned
5  |       4       |    b-cloned
6  |       5       |    c-cloned

Endgültige Baumstruktur

+--a (1)
|  +--b (2)
|     +--c (3)
|
+--a-cloned (4)
|  +--b-cloned (5)
|     +--c-cloned (6)

Die IDs sind nicht immer schön verteilt, wie diese Demodaten zeigen, so dass ich nicht immer davon ausgehen kann, dass die ID des Elternteils um 1 kleiner ist als die aktuelle ID für Zeilen, die Eltern haben.

Außerdem versuche ich, dies in T-SQL zu tun (für Microsoft SQL Server 2005 und höher).

Dies ist eine klassische Übung, die eigentlich eine reine SQL-Antwort haben sollte, aber ich bin zu sehr ans Programmieren gewöhnt, als dass ich in relationalem SQL denken könnte.

Answer 1

Versuchen Sie dies, basierend auf einer Abfrage von Quassnoi Der Artikel Adjacency List vs. Nested Sets: SQL-Server :

WITH q AS
(
    SELECT  h.*, 1 AS level
    FROM    Table1 h
    WHERE   id = 3
    UNION ALL
    SELECT  hp.*, level + 1
    FROM    q
    JOIN    Table1 hp
    ON      hp.id = q.ParentGroupID
), q2 AS (
    SELECT
        ID,
        ParentGroupID,
        SomeValue,
        (SELECT MAX(level) FROM q) - level AS level
    FROM q
)
INSERT INTO table1
SELECT
    (SELECT MAX(ID) FROM Table1) + level + 1 AS ID,
    CASE WHEN level = 0 THEN -1
         ELSE (SELECT MAX(ID) FROM Table1) + level
    END AS ParentGroupID,
    SomeValue + '-cloned'
FROM    q2

Ergebnis bei Ausführung mit Ihren Testdaten:

ID  ParentGroupID  SomeValue  
1   -1             a          
2   1              b          
3   2              c          
4   -1             a-cloned
5   4              b-cloned
6   5              c-cloned

Answer 2

Die Annahme, dass ID eine Identitätsspalte mit automatisch zugewiesenen Werten ist. Ich mache das aus dem Stegreif, also entschuldige ich mich für etwaige Syntaxfehler. Ich hoffe, die Kommentare machen die Absicht klar.

/* Find all ancestors for a given ID */
WITH Ancestors(ChildID, AncestorID) AS
(
  SELECT id AS ChildID, id As AncestorID
     FROM YourTable WHERE ParentGroupID=--1
  UNION ALL
  SELECT a.ChildID, d.ParentGroupID
     FROM  Ancestors AS a INNER JOIN
       YourTable d ON d.ID = a.AncestorID
     WHERE (b1.ParentGroupID <> -1))
),
/* Insert new rows for all ancestors of a given ID and save the results, so we have access to the new ID. we also have a column for the old ID. */
IDMap(ID, ParentGroupID, SomeValue, OldID) AS
{
    // insert, using identity column assigned ID, and save the old ID
   INSERT INTO YourTable SELECT d.ParentGroupID, d.SomeValue+"-cloned", d.ID FROM YourTable d
      INNER JOIN Ancestors a ON a.ChildID = d.ID 
      WHERE a.AncestorID=<the ID to clone>
}
/* Now update the parentID in the inserted data to the new ID */
UPDATE YourTable
SET ParentGroupID = map.ID
FROM YouTable t INNER JOIN (SELECT * FROM IDMap) map
  ON t.ParentGroupID=map.OldID