找到祖父母那一排 a2 你可以做：

select c.*
from t a
join t b on b.id = a.parentid
join t c on c.id = b.parentid
where a.name = 'a2'

找到一排排的孙子 a2 你可以做：

select c.*
from t a
join t b on b.parentid = a.id
join t c on c.parentid = b.id
where a.name = 'a2'

您可以使用递归cte方法检查父级或子级：

with r_cte as (
     select t.id, t.parentid, t.name
     from table t
     where t.name = 'a2'
     union all
     select tm.id, tm.parentid, tm.name
     from table tm inner join
          r_cte r
          on r.parentid = tm.id
)
select rc.*
from r_cte rc;