在MySQL 8.0中,您可以使用递归查询来做到这一点:
with recursive cte as (
select id,parent_id,0 lvl from mytable where id = 15
union all
select c.id,t.parent_id,c.lvl + 1
from mytable t
inner join cte c on c.parent_id = t.id
where t.parent_id > 12
)
select id,parent_id
from cte c
where c.lvl = (select max(c1.lvl) from cte c1 where c1.id = c.id)
递归cte以id = 15
开始,然后遵循关系树,直到其id严格小于12
的父级。我们跟踪层次结构中的级别。然后,外部查询将对具有最高级别的记录进行过滤。
Demo on DB Fiddle :
id | parent_id
-: | --------:
15 | 13
,
您可以使用递归CTE查找给定id
值的所有父项,并为其分配一个level
来指示它们与原始id
值的距离。然后,只是找到最大id
且也大于12的level
值的情况:
WITH RECURSIVE CTE AS
(
SELECT id,1 AS level
FROM data
WHERE id = 15
UNION ALL
SELECT data.id,data.parent_id,CTE.level + 1
FROM data
JOIN CTE ON data.id = CTE.parent_id
WHERE data.id > 12
)
SELECT id
FROM CTE
WHERE level = (SELECT MAX(level) FROM CTE)
输出
13
Demo on dbfiddle
本文链接:https://www.f2er.com/2994089.html