如何从MySql表中获取所有父母和祖先

Question

我使用的是MySql和PHP，我有这个表，其中每个项都可以包含其他项等等…

MyTable

RowId | ItemId | ChildItemId
1     | 1      | NULL
2     | 2      | NULL
3     | 3      | 1
4     | 4      | 1
5     | 4      | 2
6     | 5      | 3
7     | 5      | 4

挑战：获得所有家长

我想要一个查询，从给定的ChildItemId中获取任何层次结构级别的所有父代/祖先。

预期结果

如果我提供ChildItemId=1

AllParents
3
4
5

有查询、循环、CTE、php代码或任何解决方案的帮助吗？

Answer 1

在CTE中，您可以通过使用递归调用生成所有路由表来获取所有的父/祖先。以下查询在生成表后按TargetItemId进行筛选。

with recursive Ancesters as (
select 1 as Level, ChildItemId as TargetItemId, RowId, ItemId as AncesterId, ChildItemId
from MyTable
where ChildItemId is not null
union all
select a.Level+1, a.TargetItemId, m.RowId, m.ItemId, m.ChildItemId
from MyTable m inner join Ancesters a
on m.ChildItemId = a.AncesterId
)
select distinct AncesterId from Ancesters where TargetItemId=1

您也可以提前按ChildItemId进行筛选。

with recursive Ancesters as (
select 1 as Level, ChildItemId as TargetItemId, RowId, ItemId as AncesterId, ChildItemId
from MyTable
where ChildItemId=1
: