php 父子多层结构,父子关系层次结构(多个子)-SQL Server

表格结构。(包括循环引用)

Parent Child1 Child2

--====== ======== ========

0 1000 NULL

1000 1001 NULL

1001 1002 1003

1002 1004 1005

1003 1005 NULL

1004 1002 NULL

1005 1006 NULL

我想要的数据集是(Child2-1003&1005在有孩子的情况下成为家长,但仅限于最后一个孩子-没有Child1-1004这样的循环[仅保留孩子])

Parent Child

--====== ========

0 1000

1000 1001

1001 1002

1002 1003

1002 1004

1003 1005

1005 1006

有人能帮忙吗?

我试过下面的代码-

INSERT INTO @WorkflowStop (ParentId, ChildId,AdvChild, StopName)

SELECT ISNULL(tWrkflwArrow.osoObjectTemplateProcessStepBeginning_otsID,0) AS ParentId,

ISNULL(tWrkflwArrow.osoObjectTemplateProcessStepEnding_otsID,0) AS ChildId,

ISNULL(tWrkflwArrow.osoObjectTemplateProcessStepAutoAdvance_otsID,0) AS AdvChild,

ISNULL(tWrkflwStop.otsInternalName,tWrkflwStop.otsExternalName) AS StopName

FROM ABC.[dbo].[Workflow] AS tWrkflw

INNER JOIN DEF AS tWrkflwStop

ON tWrkflwStop.otsObjectTemplateCategory_otcID = tWrkflw.otcID

INNER JOIN GHI AS tWrkflwArrow

ON tWrkflwArrow.osoObjectTemplateProcessStepEnding_otsID = tWrkflwStop.otsID

WHERE tWrkflw.otcID = @WorkflowDefinitionId

INSERT into @WorkflowStopHierarchy (ParentId, ChildId,AdvChild, StopName,[Level])

SELECT tWrkflwStop.ParentId,

tWrkflwStop.ChildId,

tWrkflwStop.AdvChild,

tWrkflwStop.StopName,

0 as [Level]

FROM @WorkflowStop AS tWrkflwStop

WHERE tWrkflwStop.ParentId = 0;

WHILE @@ROWCOUNT > 0

BEGIN

INSERT INTO @WorkflowStopHierarchy (ParentId, ChildId,AdvChild, StopName,[Level])

SELECT tWrkflwStop.ParentId,

tWrkflwStop.ChildId,

tWrkflwStop.AdvChild,

tWrkflwStop.StopName,

tHierarchy.[Level] + 1 as [Level]

FROM @WorkflowStop AS tWrkflwStop

INNER JOIN @WorkflowStopHierarchy AS tHierarchy

ON (tWrkflwStop.ParentId = tHierarchy.ChildId) OR (tWrkflwStop.ParentId = tHierarchy.AdvChild)

WHERE (tWrkflwStop.ChildId NOT IN (select ChildId from @WorkflowStopHierarchy)) OR (tWrkflwStop.AdvChild NOT IN (select AdvChild from @WorkflowStopHierarchy))

END;

WITH CTE AS

(

SELECT tHierarchyChild.ParentId,

tHierarchyChild.ChildId,

tHierarchyChild.StopName,

ROW_NUMBER() OVER(PARTITION BY tHierarchyChild.ChildId ORDER BY tHierarchyParent.[Level], tHierarchyChild.parentId) as RowNumber

FROM @WorkflowStopHierarchy AS tHierarchyChild

LEFT JOIN @WorkflowStopHierarchy AS tHierarchyParent

ON tHierarchyChild.ParentId = tHierarchyParent.ChildId

)

SELECT @WorkflowDefinitionId,

ParentId,

ChildId,

StopName

FROM CTE

WHERE RowNumber = 1

ORDER BY ParentId,ChildId