数据库sql递归

假设现在有表Student

字段名      描述
id           主键
name         名字
pid 父节点   （关联主键）

Sqlserver

查询id为1的数据的所有子节点

with functiontable as (
    select * from student id=1
    uninon all
    select student.* from student,functiontable
    where student.pid=functiontable.id
)
select * from from functiontable

查询id为1的数据的所有父节点

with functiontable as (
    select * from student id=1
    uninon all
    select student.* from student,functiontable
    where student.id=functiontable.pid
)
select * from from functiontable

Oracle

查询id为1的数据的所有子节点

select * from student
start with id=1
connect by prior id=pid

查询id为1的数据的所有父节点

select * from student
start with id=1
connect by id=prior pid