问题描述
我有一个表，其中包含树状数据(分层设计).这是一个小样本:
I have a table that contains tree-like data (hierarchic design). Here is a small sample:
+----+----------+-----------+-------+----------+---------+
| 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是元素在一个分支中的排名.例如，ID 1, 2 and 7在同一节点上，而3 and 4在另一个节点上.FullPath使用ID显示订单(出于系统使用和性能方面的原因).
Project indicates which project owns the hierarchic datasetParentID is the ID of the parent node, it has a foreign key on ID.Order is the rank of the element in one branch. For example, IDs 1, 2 and 7 are on the same node while 3 and 4 are in another.FullPath shows the order using the ID (it's for system use and performance reasons).
Hierarchy是显示给用户的列，该列显示UI的层次结构.它会在每次插入，更新和删除后自动进行计算，这就是我遇到的问题.
Hierarchy is the column displayed to the user, which displays the hierarchy to the UI. It auto calculates after every insert, update and delete, and it's the one I'm having issues.
我为表中的删除元素创建了一个过程.它接收要删除的元素的ID作为输入，并删除它的子元素(如果有的话).然后，它重新计算FullPath和Order Column.
I created a procedure for deletion elements in the table. It receives as input the ID of the element to delete and deletes it, along with it's children if any. Then, it recalculates the FullPath and the Order Column .That works.
问题是当我尝试更新Hierarchy列时.我使用以下过程:
Problems is when I try to update the Hierarchy column. I use this procedure:
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).我添加了订单，以确保父母先更新，但没有更改.
This fails when I delete items that have lower order than others and they have children.For example, if I delete the item 3, item 4 Hierachy will be corrected (1.1), BUT its child won't (it will stay at 1.2.1, while it should be 1.1.1). I added the order by to make sure parents where updated first, but no change.
我的错误是什么，我真的不知道该如何解决.
What is my error, I really don't know how to fix this.
推荐答案
我设法通过CTE更新了层次结构.由于有了订单，因此可以根据已更新的上一个分支(父)将其附加到Hierarchy.
I managed to update the hierarchy with a CTE. Since I have the order, I can append it to Hierarchy, based on the previous branch (parent) who is already updated.
;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
这篇关于删除行后更新层次结构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！