删除行后更新层次结构

Question

我有一个包含树状数据的表(分层设计(。这是一个小样本：

+----+----------+-----------+-------+----------+---------+
| ID | ParentID | Hierarchy | Order | FullPath | Project |
+----+----------+-----------+-------+----------+---------+
|  1 | null     |         1 |     1 | 1        |       1 |
|  2 | null     |         2 |     2 | 2        |       1 |
|  3 | 1        |       1.1 |     1 | 1-3      |       1 |
|  4 | 1        |       1.2 |     2 | 1-4      |       1 |
|  5 | 4        |     1.2.1 |     1 | 1-4-5    |       1 |
|  6 | 2        |       2.1 |     1 | 2-6      |       1 |
|  7 | null     |         3 |     1 | 1        |       2 |
+----+----------+-----------+-------+----------+---------+

Project表示哪个项目拥有分层数据集ParentID是父节点的ID，它在ID上有一个外键。Order是一个分支中的元素的秩。例如，ID1, 2 and 7在同一节点上，而3 and 4在另一节点上。FullPath显示使用ID的顺序(出于系统使用和性能原因(。

Hierarchy是显示给用户的列，它显示了UI的层次结构。它在每次插入、更新和删除后都会自动计算，这就是我遇到的问题。

我创建了一个删除表中元素的过程。它接收要删除的元素的ID作为输入，并删除它，以及它的子元素(如果有的话(。然后，它重新计算FullPath和Order Column。这很管用。

问题是当我尝试更新Hierarchy列时。我使用这个程序：

SELECT  T.ID,
T.ParentID,
CASE WHEN T.ParentID IS NOT NULL THEN 
CONCAT(T1.Hierarchy, '.', CAST(T.Order AS NVARCHAR(255))) 
ELSE 
CAST(T.Order AS NVARCHAR(255))
END AS Hierarchy
INTO    #tmp
FROM    t_HierarchyTable T
LEFT JOIN   t_HierarchyTable T1
ON  T1.ID = T.ParentID
WHERE Project = @Project --Variable to only update the current project for performance
ORDER BY T.FullPath
--Update the table with ID as key on tmp table

当我删除顺序比其他项目低的项目并且这些项目有子项目时，此操作将失败。例如，如果我删除了第3项，第4项Hierachy将被更正(1.1(，但它的子项不会(它将保持在1.2.1，而它应该是1.1.1(。我通过添加顺序来确保父母首先更新，但没有更改。

我的错误是什么，我真的不知道该怎么解决。

Answer 1

我设法用CTE更新了层次结构。由于我有订单，我可以根据已经更新的上一个分支(父分支(将其附加到Hierarchy。

;WITH CODES(ID, sCode, iLevel) AS 
(
SELECT 
T.[ID]                                  AS [ID],
CONVERT(VARCHAR(8000), T.[Order])       AS [Hierarchy],
1                                       AS [iLevel]
FROM 
[dbo].[data] AS T
WHERE 
T.[ParentID] IS NULL
UNION ALL
SELECT 
T.[ID]                                      AS [ID],
P.[Hierarchy] + IIF(RIGHT(P.[Hierarchy], 1) <> '-', '-', '') + CONVERT(VARCHAR(8000), T.[Order])    AS [Hierarchy],
P.[iLevel] + 1                              AS [iLevel]
FROM 
[dbo].[data] AS T
INNER JOIN CODES AS P ON 
P.[ID] = T.[ParentID]
WHERE
P.[iLevel] < 100
)
SELECT 
[ID], [Hierarchy], [iLevel]
INTO
#CODES
FROM 
CODES