根据Quassnoi的文章“邻接列表与嵌套集：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
602392714
清零编程群