sql server查询所有上级/下级

最新推荐文章于 2023-08-04 17:25:59 发布

原创最新推荐文章于 2023-08-04 17:25:59 发布 · 2.2k 阅读

1 ·

CC 4.0 BY-SA版权

sql server 专栏收录该内容

3 篇文章

订阅专栏

本文介绍了一种使用SQL的WITH语句进行递归查询的方法，以获取学校中所有学生及其可能存在的上下级关系。通过两次不同的WITH子句定义，分别展示了如何从sys_student表中直接选择满足条件的学生信息，并通过递归联合查询来获取学生的上下级关系。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//上级
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