请教数据库查询

Question

如何查询出来tid为1的所有子目录
目前状态
tid         tname                                              tparent     tinfo
----------- -------------------------------------------------- ----------- --------
1           test1                                              0           test1
2           test2                                              0           test2
3           test3                                              1           test3
4           test4                                              2           test4
5           test5                                              3           test5
6           test6                                              4           test6结果状态
tid         tname                                              tparent     tinfo
----------- -------------------------------------------------- ----------- --------
1           test1                                              0           test1
3           test3                                              1           test3
5           test5                                              3           test5

Answer 1

with cte as
(
    select tid,tparent
    from tb
    where tid = 1  --查询的节点值
    union all
    select e.tid,e.tparent
    from tb e join cte b on e.tparent = b.id
)select *
from tb
where tid in (select tid from cte)