在现有父子关系表中填充hierarchyid列？
我搜索一种填充现有父子表的方法：
Tabelle: Categories
-------------------
Columns: CategoryID
ParentID
;WITH CTE (CategoryID, ParentID, HierarchyString) AS (
SELECT CategoryID, ParentID, HierarchyString = CAST(ParentID AS VARCHAR(8000)) + '/' + CAST(CategoryID AS VARCHAR(8000))
FROM Categories
UNION ALL
SELECT r.CategoryID, r.ParentID, HierarchyString = CAST(p.CategoryID AS VARCHAR(8000)) + '/' + CAST(r.ParentID AS VARCHAR(8000))
FROM Categories r
INNER JOIN CTE p ON r.CategoryID = p.ParentID
)
SELECT * FROM CTE;
没有找到相关结果
已邀请:
1 个回复
徐百晴墓斜
;WITH CTE (CategoryID, ParentID, Hierarchy) AS ( SELECT CategoryID, ParentID, Hierarchy FROM Categories UNION ALL SELECT p.CategoryID, r.ParentID, hierarchyid::Parse(REPLACE((r.Hierarchy.ToString() + p.Hierarchy.ToString()), '//', '/')) FROM Categories r INNER JOIN CTE p ON r.CategoryID = p.ParentID ) SELECT CTE.CategoryID, R.ParentID, CTE.Hierarchy.ToString() FROM CTE INNER JOIN Categories AS R ON CTE.CategoryID = R.CategoryID WHERE CTE.Hierarchy.ToString() LIKE '%/' + CAST(CTE.ParentID AS VARCHAR) + '/' + CAST(CTE.CategoryID AS VARCHAR) + '/%' OR CTE.ParentID = 0