使用SQL在数据库中克隆表示树形结构的数据

Question

给定一个表示层次树结构的表，它有三列：

1. ID（主键，非自增）
2. ParentGroupID
3. SomeValue

我知道该分支的最底层节点，并且我想将其复制到具有相同父级数量的新分支中，这些父级也需要被克隆。

我正在尝试编写一个单个的SQL INSERT INTO语句，它将复制与第一组ID相同的每一行到一个新的GroupID中。

例如，开始时的表格如下：

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

运行简单的INSERT INTO语句后我的目标是什么：

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

最终的树状结构

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

这个演示数据并不总是有良好的间隔，所以对于具有父级的行，我不能总是假设父级ID比当前ID小1。

此外，我正在尝试在T-SQL（适用于Microsoft SQL Server 2005及更高版本）中完成此操作。

这感觉像一个经典的练习题，应该有一个纯SQL的答案，但我太习惯编程了，我的思维无法转换到关系型SQL。

- AmoebaMan17

---

您使用的是哪个版本的SQL Server？ - Tom H

我不清楚克隆的ParentGroupID应该如何确定。B的克隆是如何获得ParentGroupId为4，而A的克隆获得与其源行相等的ParentGroupId的？ - Thomas

我需要支持SQL Server 2005。 - AmoebaMan17

ID是什么类型的字段 - 身份列还是手动分配的整数类型？ - mdma

想法是保持新分支的相同树形结构。当我需要克隆与treenode c（ID==3）相关联的某些数据时，我需要创建一个新节点，然后具有相同数量的父节点。我还在原始帖子中添加了最终的树形结构，因为我可以在评论中进行花式格式化。我认为我的ID列出得正确...我只是试图通过一个简单的示例来伪造虚拟数据。 - AmoebaMan17

ID 是手动分配的整数。 - AmoebaMan17

Answer 1

根据 Quassnoi 的文章 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

在您的测试数据上运行时的结果：

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

- Mark Byers

---

我要试一下！再次感谢马克！我需要开始赶上你和Quassnoi的帖子，因为你们总是有答案！ - AmoebaMan17

Answer 2

假设ID是一个自动分配值的身份列。

我随意编写了这个代码，如果有语法错误请见谅。我希望注释可以清楚表达我的意图。

/* 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

- mdma

---

我没有看到任何对 @@identity 的调用，你从哪里获取它？ - Omu

我同意，我也没有看到。我期望在IDMap CTE上有一个OUTPUT子句。也许它被深夜的脑雾所困扰。我会更新查询。 - mdma