oracle 查询树结构 select ... start with ...connect by piror

最新推荐文章于 2022-07-18 23:30:29 发布

原创最新推荐文章于 2022-07-18 23:30:29 发布 · 315 阅读

0 ·

CC 4.0 BY-SA版权

本文介绍如何使用Oracle SQL的START WITH...CONNECT BY子句查询树形结构数据，包括查询下级子节点及多表链接查询。通过CASE语句判断节点类型，如枝节点、叶子节点。示例查询展示了如何从指定节点开始，递归连接查询子节点，同时考虑了查询条件和节点层级的排序。

查询树结构

查询下级子节点

select par_id,
p_par_id,
level,！--查询树节点层级
CASE
WHEN CONNECT_BY_ISLEAF = 0 THEN !-- CONNECT_BY_ISLEAF 是伪列查询是否是叶子节点
'枝'
WHEN CONNECT_BY_ISLEAF = 1 THEN
'叶子'
ELSE
'其他'
END
from par_real_info
start with par_id = 'tj000001' !-- start with后跟的是子节点则会查出根节点相关数据，如果是父节点则不会查出父节点相关数据
connect by prior par_id = p_par_id !--prior 在子节点侧查询子节点

order by level;

//多表链接查询

select t.par_name,

t.par_id,
r.endda,
level,
CASE CONNECT_BY_ISLEAF
WHEN 0 THEN
'枝'
WHEN 1 THEN
'叶子'
ELSE
'其他'
END
from par_baseinfo t, par_real_info r
start with t.par_id = 'tj000001'
and t.in_use_or_revoke = '1'
and r.endda = '99991231'
and t.par_id = r.par_id
connect by prior r.par_id = r.p_par_id
and t.in_use_or_revoke = '1'
and r.endda = '99991231'
and t.par_id = r.par_id
order by level;