Oracle 语句递归查找父子关系语句

最新推荐文章于 2024-06-05 11:13:52 发布

陈勇士

最新推荐文章于 2024-06-05 11:13:52 发布

阅读量2.1k

点赞数

分类专栏： SQL Oracle

本文链接：https://blog.youkuaiyun.com/chenyongtu110/article/details/46863719

版权

Oracle 同时被 2 个专栏收录

8 篇文章

订阅专栏

SQL

2 篇文章

订阅专栏

使用Oracle数据库查询语句，通过dept_name包含'科'字符和parent_id为'302'作为起点，递归查找b_Jt_Departments表中的父子部门关系，构建层级结构。

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

根据部门模糊搜索，后台会自动拼树

select t.*,level from b_Jt_Departments t start with dept_name like '%科%' and parent_id='302' connect by prior t.dept_id=t.parent_id

 --通过根节点遍历子节点
select t.*,level from family t start with parentid=1 connect by prior id=parentid; 

--通过子节点向根节点追溯
select t.*,level from family t start with id=5 connect by prior parentid=id;        

--查找直接子节点（下一层）
select t.*,level from family t where level = 2 start with parentid=1 connect by prior id=parentid;  

 --查找直接父节点（上一层）
select t.*,level from family t where level = 2 start with id=5 connect by prior parentid=id;       

--通过根节点遍历子节点
select level,t.* from ORG_DEPARTMENT_INFO t start with parent_id=-1 connect by prior dept_id=parent_id;  
--查找直接子节点（下一层）
select level,t.* from ORG_DEPARTMENT_INFO t where level = 2 start with parent_id=-1 connect by prior dept_id=parent_id;  
 --通过子节点向根节点追溯
select level,t.* from ORG_DEPARTMENT_INFO t start with dept_id=10000260 connect by prior parent_id=dept_id;       
--查找直接父节点（上一层）
select level,t.* from ORG_DEPARTMENT_INFO t where level = 2 start with dept_id=10000260 connect by prior parent_id=dept_id;     
 --查找直接父节点（上一层）   
select t.* from ORG_DEPARTMENT_INFO t where level = 2 start with dept_id=10000260 connect by prior parent_id=dept_id;       

--通过根节点向子节点追溯

select level, t.*from SM_ORGANIZATION t start with t.org_id='114e0e3c-dbd1-4c2e-9d10-d5fd1e243961' connect by prior org_id=parent_id; 
 --通过子节点向根节点追溯
select level, t.*  from SM_ORGANIZATION t start with org_id='9e67e1f4-f4b0-4669-b4e1-1dca132bb4d7' connect by prior parent_id=org_id;