如何从mysql表中获取所有父级和祖先级?

0s0u357o  于 2021-07-24  发布在  Java
关注(0)|答案(1)|浏览(613)

我使用的是mysql和php,我有一个表,其中每个项目可以包含其他项目等等。。。
空白表

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代码或任何解决方案有帮助吗?

dwbf0jvd

dwbf0jvd1#

在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
  :

相关问题