使用 SQL 克隆数据库中表示的树结构 [英] Use SQL to clone a tree structure represented in a database

问题描述

给定一个表示分层树结构并具有三列的表

Given a table that represents a hierarchical tree structure and has three columns

1. ID(主键，非自动递增)
2. ParentGroupID
3. SomeValue

我知道该分支的最低节点，我想将其复制到一个新分支，该分支的父节点数量也需要克隆.

I know the lowest most node of that branch, and I want to copy that to a new branch with the same number of parents that also need to be cloned.

我正在尝试编写一个 SQL INSERT INTO 语句，该语句会将属于同一主的每一行复制到一个新的 GroupID 中.

I am trying to write a single SQL INSERT INTO statement that will make a copy of every row that is of the same main has is part one GroupID into a new GroupID.

示例起始表:

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

运行简单的 INSERT INTO 语句后的目标:

Goal after I run a simple INSERT INTO statement:

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 比有父行的行的当前 ID 小 1.

The IDs aren't always nicely spaced out as this demo data is showing, so I can't always assume that the Parent's ID is 1 less than the current ID for rows that have parents.

此外，我正在尝试在 T-SQL(适用于 Microsoft SQL Server 2005 及更高版本)中执行此操作.

Also, I am trying to do this in T-SQL (for Microsoft SQL Server 2005 and greater).

这感觉像是一个经典的练习，应该有一个纯 SQL 的答案，但我太习惯于编程，我的大脑不会在关系 SQL 中思考.

This feels like a classic exercise that should have a pure-SQL answer, but I'm too used to programming that my mind doesn't think in relational SQL.

推荐答案

试试这个，基于来自 Quassnoi 的查询的文章 邻接列表与嵌套集:SQL Server:

Try this, based on a query from Quassnoi's article 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

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

Result when run on your test data:

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

这篇关于使用 SQL 克隆数据库中表示的树结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！