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, s.parent_dept_id, LPAD(' ', 5 * (LEVEL - 1)) || s.DEPT_CODE DEPT_CODE, s.order_by, 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, 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' order siblings by s.dept_code;

--树排序2(递归后整体排序，树形结构打乱)
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' order by s.dept_code;

--叶子节点(connect_by_isleaf)
select LPAD(' ', 5 * (LEVEL - 1)) || s.DEPT_CODE DEPT_CODE,
       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(' ', 5 * (LEVEL - 1)) || s.DEPT_CODE DEPT_CODE,
       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(' ', 5 * (LEVEL - 1)) || s.DEPT_CODE DEPT_CODE,
       CONNECT_BY_ISLEAF leaf,
       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;