T-SQL如何将层次结构级别更改为升级而不是降级？

-- 创建临时表
CREATE TABLE #TempHierarchy
(
    ID INT PRIMARY KEY,
    Name VARCHAR(100),
    Level INT
);

-- 插入初始数据
INSERT INTO #TempHierarchy (ID, Name, Level)
VALUES (1, 'Node A', 1),
       (2, 'Node B', 1),
       (3, 'Node C', 2),
       (4, 'Node D', 2),
       (5, 'Node E', 3),
       (6, 'Node F', 3);

-- 更新层次结构级别
WITH RecursiveCTE AS (
    -- 初始查询
    SELECT ID, Name, Level, CAST(NULL AS INT) AS ParentLevel
    FROM #TempHierarchy
    WHERE Level = 3 -- 目标级别
    UNION ALL
    -- 递归查询
    SELECT T.ID, T.Name, T.Level, RC.ParentLevel + 1 AS ParentLevel
    FROM #TempHierarchy T
    JOIN RecursiveCTE RC ON T.ID = RC.ParentLevel
)
UPDATE T
SET T.Level = RC.ParentLevel
FROM #TempHierarchy T
JOIN RecursiveCTE RC ON T.ID = RC.ID;

-- 查看更新后的层次结构
SELECT * FROM #TempHierarchy;

-- 删除临时表
DROP TABLE #TempHierarchy;