一个表中有自身id，父亲id，母亲id 如何追溯4代

Question

一个表中有自身id，父亲id，母亲id 如何追溯4代，最终还是要三列，只是要追溯四代

Answer 1

MySQL8+ CTE递归

with recursive cte as (
select p, father, mother, 1 count from table_name where p = 'pid'
union all 
select t1.p, t1.father, t1.mother, c.count +1 from table_name t1, cte c where c.father = t1.p or c.mother = t1.p
) select * from cte where count <= 4