問題描述
如何使用 CTE 從分層視圖中獲取元素 (How to get elements from a hierarchical view with CTE)
I have a table that store recursive records through two fields: ID and PARENTID.
I have a functionality that can associate a parent to an element of the tree. When I select the elements that can be "parent" of myself I shall obviously exclude from the resulting list all the elements which, directly or indirectly, depends on me but also the elements from which I already depend.
Let's make an example. Given the following sample hierarchy:
ID PARENT_ID
‑‑‑‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
1 NULL
2 1
3 NULL
4 2
5 1
6 3
If I would like to find the elements that can be parent of element with ID = 4 I shall consider only elements 5 ‑ 3 ‑ 6 because they do not have any relation with the actual structure.
How can I get those elements with a CTE query?
‑‑‑‑‑
參考解法
方法 1:
Select *
into #tmp
From Tree2
;WITH Rollups AS (
SELECT ID, Parent_Id
FROM tree2 where ID=4
UNION ALL
SELECT parent.Id, parent.Parent_Id
FROM tree2 parent
INNER JOIN Rollups child ON child.Id = parent.Parent_Id
)
Delete #tmp from Rollups where #tmp.ID=Rollups.ID
;WITH Rollups AS (
SELECT ID, Parent_Id
FROM tree2 where ID=4
UNION ALL
SELECT parent.Id, parent.Parent_Id
FROM tree2 parent
INNER JOIN Rollups child ON child.Parent_Id = parent.Id
)
Delete #tmp from Rollups where #tmp.ID=Rollups.ID
Select * from #tmp
Drop Table #tmp