ORACLE-SQL 关于树结构的查询

1、通常的树结构包括节点编码NODE_CODE，节点名称NODE_NAME，父节点编码PARENT_ID

2、另外还可以拥有一些控制字段，排序SORT_NO,节点层级NODE_LEVEL,是否显示IS_SHOW,是否叶子节点IS_LEAF

3、除此之外，偶尔包含：唯一标识ID,创建人REC_CREATOR，创建时间REC_CREATE_TIME，修改人REC_REVISOR，修改时间REC_REVISE_TIME

4、一般的，默认根节点ID为ROOT，即树展开第一层节点的父节点编码为root。数字1标识是，数字0标识否。

以下面数据表举例

ID	NODE_CODE	NODE_NAME	PARENT_ID	NODE_LEVEL	IS_SHOW	IS_LEAF	SORT_NO
1	100	公司1	root	1	1	0	1
2	110	部门1	100	2	1	0	2
3	111	小组1	110	3	1	1	3
4	120	部门2	100	2	1	1	4
5	200	公司2	root	1	1	0	5
6	210	部门3	200	2	1	0	6
7	211	小组2	210	3	1	1	7
8	220	部门4	200	2	1	1	8

一、简单的级联查询

1、从root到叶子 （root也可以是任意的树节点，即查询以该节点为根的树）

select NODE_CODE, NODE_NAME, PARENT_ID, NODE_LEVEL
  from TREE --具有子接点ID与父接点ID的表 
 start with PARENT_ID= 'root' --给定一个startid(字段名为子接点ID，及开始的ID号)
connect by prior NODE_CODE = PARENT_ID--联接条件为子接点等于父接点，不能反

2、从叶子到root(从小组2到公司2)

  select NODE_CODE,
         NODE_NAME,
         PARENT_ID,
         NODE_LEVEL 
   start with NODE_CODE = '211' --给定一个startid(字段名为子接点ID，及开始的ID号)
  connect by prior PARENT_ID= NODE_CODE --联接条件为子接点等于父接点，不能反

二、复杂的级联查询

1、查询 小组1的代码，以及级联的部门，公司信息（反之把desc换成asc）

111	小组1-部门1-公司1（反之把desc换成asc）

 select M_CODE,
        listagg(NODE_NAME, '-') within group(order by NODE_CODE desc) AS NAME
   from (select t.*, CONNECT_BY_ROOT(NODE_CODE) M_CODE
           from TREE t
          start with NODE_CODE = '111'
         connect by NODE_CODE = prior PARENT_ID
          order by NODE_CODE asc)
  group by M_CODE;

2、显示所有叶子节点，代码，以及级联的部门，公司信息（反之把desc换成asc）

111	小组1-部门1-公司1（反之把desc换成asc）
120	部门2-公司1
211	小组2-部门3-公司2
220	部门4-公司2

select M_CODE,
        listagg(NODE_NAME, '-') within group(order by NODE_CODE ASC) AS NAME
       
  from (select t.*, CONNECT_BY_ROOT(NODE_CODE) M_CODE
          from TREE t
         start with NODE_CODE in (select NODE_CODE
                                    from TREE t1
                                   WHERE IS_LEAF= '1')
        connect by NODE_CODE = prior PARENT_ID
         order by NODE_CODE asc)
 group by M_CODE

三、根据层级展示树节点结构（适用于查询人员组织机构信息）

组织代码	层级1	层级2	层级3
111	公司1	部门1	小组1
120	公司1	部门2
211	公司2	部门3	小组2
220	公司2	部门4

SELECT M_CODE,
       listagg(CASE NODE_LEVEL
                 WHEN '1' THEN
                  NODE_NAME
                 ELSE
                  ''
               END) within group(order by NODE_LEVEL ASC) AS L1,
       listagg(CASE NODE_LEVEL
                 WHEN '2' THEN
                  NODE_NAME
                 ELSE
                  ''
               END) within group(order by NODE_LEVEL ASC) AS L2,
       listagg(CASE NODE_LEVEL
                 WHEN '3' THEN
                  NODE_NAME
                 ELSE
                  ''
               END) within group(order by NODE_LEVEL ASC) AS L3
  FROM (select NODE_NAME, NODE_LEVEL, CONNECT_BY_ROOT(NODE_CODE) M_CODE
          from TREE t
         start with NODE_CODE in (select NODE_CODE
                                    from TREE t1
                                   WHERE IS_LEAF= '1' --叶子节点
                                  )
        connect by NODE_CODE = prior PARENT_ID)
 GROUP BY M_CODE

1、场面有几个注意点：横表转竖表、对文字进行求和（listagg）