Oracle-树查询（层次查询）或多列转行sys_connect_by_path

发表于 分类于 本文字数： 2.7k 阅读时长 ≈ 2 分钟
摘要：如何使用Oracle系统标准函数，拼接组织路径，审批链，上下级关系等。
1
2
3
--Oracle函数:sys_connect_by_path 主要用于树查询(层次查询) 以及 多列转行。其语法一般为：
select ... sys_connect_by_path(column_name,'connect_symbol') from table 
start with ... connect by ... prior

理解：对于数据库来说，根节点并不一定是在数据库中设计的顶级节点，而是start with开始的地方。sys_connect_by_path函数就是从start with开始的地方开始遍历，并记下其遍历到的节点，start with开始的地方被视为根节点，将遍历到的路径根据函数中的分隔符，组成一个新的字符串。sys_connect_by_path函数用connect by来寻找下一条记录，直到迭代找不到相应记录为止。概念与递归类似，connect by指定递归(连接)条件，如果条件不满足则递归结束。

正向查：

根据顶级节点，逐层查询所有的子部门，并拼接组织路径：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
SELECT D.SETID,
       D.TREE_NODE AS DEPTID,
       D.EFFDT,
       D.EFF_STATUS,
       D.DESCR,
       D.DESCRSHORT,
       LTRIM(SYS_CONNECT_BY_PATH(D.DESCR, '>'), '>') FULL_DESCR
  FROM (SELECT D.SETID,
               D.TREE_NODE,
               D.PARENT_NODE_NAME,     
               P.EFFDT,
               P.EFF_STATUS,
               P.DESCR,
               P.DESCRSHORT
          FROM PSTREENODE D, PSTREEDEFN T, PS_DEPT_TBL P 
         WHERE T.TREE_NAME = 'DEPT_SECURITY'
           AND T.SETID = 'NH001'
           AND T.EFFDT = (SELECT MAX(T_ED.EFFDT)
                            FROM PSTREEDEFN T_ED
                           WHERE T_ED.SETID = T.SETID
                             AND T_ED.SETCNTRLVALUE = T.SETCNTRLVALUE
                             AND T_ED.TREE_NAME = T.TREE_NAME
                             AND T_ED.EFFDT <= SYSDATE)
           AND D.SETID = T.SETID
           AND D.TREE_NAME = T.TREE_NAME
           AND D.SETCNTRLVALUE = T.SETCNTRLVALUE
           AND D.EFFDT = T.EFFDT
           AND T.EFF_STATUS = 'A'
           AND P.SETID = D.SETID
           AND P.DEPTID = D.TREE_NODE
           AND P.EFFDT = (SELECT MAX(P1.EFFDT)
                            FROM PS_DEPT_TBL P1
                           WHERE P1.DEPTID = P.DEPTID
                             AND P1.SETID = P.SETID
                             AND P1.EFFDT <= SYSDATE)) D
 START WITH D.PARENT_NODE_NAME = ' '
CONNECT BY NOCYCLE    PRIOR D.TREE_NODE =    D.PARENT_NODE_NAME

示例解析：

PARENT_NODE_NAME = ‘ ‘ –选择一条数据为顶节点

PRIOR D.TREE_NODE = D.PARENT_NODE_NAME –PRIOR 后面的字段应该为 顶节点数据中，与子数据有关联的字段，示例中看到TREE_NODE 是当前部门ID，PARENT_NODE_NAME是当前部门的父部门ID；这样就可以从定节点一条条向下遍历数据，然后对字段进行DESCR拼接。所以，等价于 D.PARENT_NODE_NAME = PRIOR D.TREE_NODE；主要是看 PRIOR 字段放在那里。

反向查

由末级节点反查到根节点，下面这个例子就是查找父部门（同样可以查询审批链、上下级关系），一直到根节点：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

SELECT D.SETID,
       D.TREE_NODE AS DEPTID,
       D.EFFDT,
       D.EFF_STATUS,
       D.DESCR,
       D.DESCRSHORT,
       LTRIM(SYS_CONNECT_BY_PATH(D.DESCR, '>'), '>') FULL_DESCR
  FROM (SELECT D.SETID,
               D.TREE_NODE,
               D.PARENT_NODE_NAME,     
               P.EFFDT,
               P.EFF_STATUS,
               P.DESCR,
               P.DESCRSHORT
          FROM PSTREENODE D, PSTREEDEFN T, PS_DEPT_TBL P 
         WHERE T.TREE_NAME = 'DEPT_SECURITY'
           AND T.SETID = 'NH001'
           AND T.EFFDT = (SELECT MAX(T_ED.EFFDT)
                            FROM PSTREEDEFN T_ED
                           WHERE T_ED.SETID = T.SETID
                             AND T_ED.SETCNTRLVALUE = T.SETCNTRLVALUE
                             AND T_ED.TREE_NAME = T.TREE_NAME
                             AND T_ED.EFFDT <= SYSDATE)
           AND D.SETID = T.SETID
           AND D.TREE_NAME = T.TREE_NAME
           AND D.SETCNTRLVALUE = T.SETCNTRLVALUE
           AND D.EFFDT = T.EFFDT
           AND T.EFF_STATUS = 'A'
           AND P.SETID = D.SETID
           AND P.DEPTID = D.TREE_NODE
           AND P.EFFDT = (SELECT MAX(P1.EFFDT)
                            FROM PS_DEPT_TBL P1
                           WHERE P1.DEPTID = P.DEPTID
                             AND P1.SETID = P.SETID
                             AND P1.EFFDT <= SYSDATE)) D
 START WITH  D.TREE_NODE = '1000003341'
CONNECT BY NOCYCLE  D.TREE_NODE = PRIOR D.PARENT_NODE_NAME