我可以在分层数据模型中找到给定记录的所有子项(请参阅下面的代码),但是我不知道如何通过给定的子代码来备份父/子链.任何人都可以指出正确的方向,找出如何做到这一点？在
Linq到sql中也是可能的吗？
WITH TaskHierarchy (TaskID,[Subject],ParentID,HierarchyLevel,HierarchyPath) AS
(
-- Base case
SELECT
TaskID,1 as HierarchyLevel,CONVERT(VARCHAR(MAX),'/') AS HierarchyPath
FROM Task
WHERE TaskID = 2
UNION ALL
-- Recursive step
SELECT
t.TaskID,t.Subject,t.ParentID,th.HierarchyLevel + 1 AS HierarchyLevel,CONVERT(varchar(MAX),th.HierarchyPath + CONVERT(VARCHAR(32),t.ParentID) + '/') AS HierarchyPath
FROM Task t
INNER JOIN TaskHierarchy th ON
t.ParentID = th.TaskID
)
SELECT *
FROM TaskHierarchy
ORDER BY HierarchyLevel,[Subject]
解决方法
啊,我想出来了
WITH Hierarchy(TaskID,IsProject,HLevel)
AS
(
SELECT
TaskID,0 as HLevel
FROM
Task
WHERE
TaskID = 59
UNION ALL
SELECT
SubDepartment.TaskID,SubDepartment.[Subject],SubDepartment.ParentID,SubDepartment.IsProject,HLevel + 1
FROM
Task SubDepartment
INNER JOIN
Hierarchy ParentDepartment
ON
SubDepartment.TaskID = ParentDepartment.ParentID
)
SELECT
TaskID,HLevel
FROM
Hierarchy
ORDER BY
HLevel DESC
