oracle 树结构的查询总结

select t.*,t.rowid from SYS_DEPT_V t

--lpad函数和伪列level模拟树形结构
select LPAD(' ',5 * (LEVEL - 1)) || s.DEPT_CODE
from SYS_DEPT_V s
start with s.parent_dept_id = '-1'
connect by prior s.id = s.parent_dept_id;

--start with 和 connect by
select s.*,level from SYS_DEPT_V s start with s.parent_dept_id = '-1'
connect by prior s.id = s.parent_dept_id;

--prior 递归方向（上和下）
select s.*,level from SYS_DEPT_V s start with s.id = '402880484105b3b101410a8bdef00096'
connect by s.id = prior s.parent_dept_id;

--排除树节点（条件与start with 一致）
select s.id,s.parent_dept_id,LPAD(' ',5 * (LEVEL - 1)) || s.DEPT_CODE DEPT_CODE,s.order_by,level
from SYS_DEPT_V s
start with s.id = '402880484105b3b101410a8ada52008e'
connect by prior s.id = s.parent_dept_id and s.id <> '402880484105b3b101410a8ada52008e';

--排除节点（条件与start with 一致）
select s.id,level
from SYS_DEPT_V s
where s.id <> '402880484105b3b101410a8ada52008e'
start with s.id = '402880484105b3b101410a8ada52008e'
connect by prior s.id = s.parent_dept_id;

--树排序1(order siblings by同部门排序)
select s.id,level
from SYS_DEPT_V s
start with s.id = '402880484105b3b101410a8ada52008e'
connect by prior s.id = s.parent_dept_id and s.id <> '402880484105b3b101410a8ada52008e' order siblings by s.dept_code;

--树排序2(递归后整体排序，树形结构打乱)
select s.id,level
from SYS_DEPT_V s
start with s.id = '402880484105b3b101410a8ada52008e'
connect by prior s.id = s.parent_dept_id and s.id <> '402880484105b3b101410a8ada52008e' order by s.dept_code;

--叶子节点(connect_by_isleaf)
select LPAD(' ',
CONNECT_BY_ISLEAF leaf
from SYS_DEPT_V s
start with s.parent_dept_id = '-1'
connect by prior s.id = s.parent_dept_id;

--根节点(connect_by_root)
select LPAD(' ',
CONNECT_BY_ISLEAF leaf,
CONNECT_BY_ROOT s.dept_code root
from SYS_DEPT_V s
start with s.parent_dept_id = '-1'
connect by prior s.id = s.parent_dept_id;

--树的路径（SYS_CONNECT_BY_PATH）
select LPAD(' ',
CONNECT_BY_ROOT s.dept_code root,
SYS_CONNECT_BY_PATH(s.dept_code,'/') "path"
from SYS_DEPT_V s
start with s.id = '402880484105b3b101410a8ada52008e'
connect by prior s.id = s.parent_dept_id;

--树的路径（wmsys.wm_concat()默认以分号隔离）
select wm_concat(s.id) treepath
from SYS_DEPT_V s
start with s.id = '402880484105b3b101410a8ada52008e'
connect by prior s.id = s.parent_dept_id;

--树的路径(很奇异)
select wmsys.wm_concat(s.id)over(order by s.id) treepath
from SYS_DEPT_V s
start with s.id = '402880484105b3b101410a8ada52008e'
connect by prior s.id = s.parent_dept_id;

--over(分组和排序) row_number() --父节点下所有子节点的值 select parent_dept_id,ltrim(sys_connect_by_path(id,'*'),'*') "values" from (select t.*,(row_number() /* 按照parentid分组，组内部按照dataid排序后的行号*/ over(partition by t.parent_dept_id order by t.id)) numid from SYS_DEPT_V t ) WHERE connect_by_isleaf = 1 start with numid = 1 connect by numid - 1 = prior numid and parent_dept_id = prior parent_dept_id;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!