sql server查询所有上级/下级

//上级
with test_student(stu_id, stu_name)
as
(
select stu_id, stu_name
from sys_student
where school_id = ‘1’ and del_flag = 0
union all
select a.parent_id, a.stu_id, a.stu_name
from sys_student a
inner join
test_student b
on a.stu_id=b.parent_id where a.del_flag = 0
)
select stu_id, stu_name from test_student order by stu_id
//下级
with test_student(stu_id, parent_id,stu_name)
as
(
select s.stu_id, s.parent_id, s.stu_name
from sys_student s
where s.school_id = ‘1’ and s.deleted_flag = 0
union all
select a.stu_id, a.parent_id, a.stu_name
from sys_student a
inner join
test_student b
on a.parent_id=b.stu_id where a.deleted_flag = 0
)
select stu_id, stu_name from test_student order by stu_id