oracle数据库在树表结构中sql语句的编写和应用

oracle数据库在树表结构中sql语句的编写和应用

1、查找树中的所有顶级父节点（辈份最长的人）。
select * from tableName where upper_group_id is null;
2、查找一个节点的直属子节点（所有儿子）。
select * from tableName where upper_group_id = ‘字段值’;
3、查找一个节点的所有直属子节点（所有后代）。
select *
from tableName
start with party_id = ‘字段值’
connect by prior party_id = upper_group_id;
4、查找一个节点的直属父节点（父亲）。 如果查找的是节点的直属父节点，也是不用用到树型查询的。c->child, p->parent。
select c.party_id,
c.group_name,
p.party_id parent_id,
p.group_name parent_title
from tableName c, tableName p
where c.upper_group_id = p.party_id
and c.party_id = ‘字段值’;
5、查找一个节点的所有直属父节点（祖宗）。
select *
from tableName m
start with m.party_id = ‘字段值’
connect by prior m.upper_group_id = m.party_id;
6、查询一个节点的兄弟节点（亲兄弟）。m.parent=m2.parent->同一个父亲
select *
from tableName m
where exists (select *
from tableName m2
where m2.upper_group_id = m.upper_group_id
and m2.party_id = ‘字段值’);
7、查询与一个节点同级的节点（族兄弟）。
如果在表中设置了级别的字段，那么在做这类查询时会很轻松，同一级别的就是与那个节点同级的，在这里列出不使用该字段时的实现!
with tmp as
(select a.*, level leaf
from tableName a
start with a.party_id = ‘字段值’
connect by a.upper_group_id = prior a.party_id)
select *
from tmp
where leaf = (select leaf from tmp where party_id = ‘字段值’);
8、查询所在树节点的完整路径，从树的根节点到当前节点路径
select sys_connect_by_path(group_name, ‘/’)
from tableName t
where party_id = ‘字段值1’
start with party_id = ‘字段值2’
connect by prior party_id = upper_group_id;
从当前节点到树的根节点路径
select sys_connect_by_path(group_name, ‘/’)
from tableName t
where upper_group_id = 1
start with party_id = ‘字段值’
connect by prior upper_group_id = party_id;