sql查询【树形结构数据】

WITH T AS
 (SELECT TO_CHAR(I.ITEMNO) ID,
         '-1' AS PID,
         TO_CHAR(I.CNNAME) AS NAME,
         0 AS ISLEAF
    FROM BO_ACT_DICT_KV_ITEM I
   WHERE DICTKEY = 'INFOPUBLICADD.CODE.INFOTYPE'
  UNION
  SELECT TO_CHAR(G.EXTTEXT2) ID,
         TO_CHAR(G.EXTTEXT1) PID,
         (SELECT TO_CHAR(R.CNNAME)
            FROM BO_ACT_DICT_KV_ITEM R
           WHERE DICTKEY = 'INFOPUBLICADD.CODE.RESTRICT'
             AND R.ITEMNO = G.EXTTEXT2) AS NAME,
         1 AS ISLEAF
    FROM BO_ACT_DICT_KV_ITEM G
   WHERE DICTKEY = 'INFOPUBLICADD.CODE.INFOTYPE_RESTRICT')
SELECT *
  FROM T
 START WITH T.PARENTID ='-1'
CONNECT BY PRIOR T.ID = T.PID

WITH AS 短语，也叫做子查询部分（subquery factoring）可认为在真正进行查询之前预先构造了一个临时表，之后便可多次使用它做进一步的分析和处理。

更多用法可访问https://www.cnblogs.com/JamesHooke/p/10964495.html这位同学有举例介绍

其中我们T中先拿出来了所有的父节点和子节点：

union

START WITH 为层级结构设计模式所使用的短语，一般语法如下：

SELECT ... FROM    + 表名
WHERE              (+ 条件3)
START WITH         + 条件1
CONNECT BY PRIOR   + 条件2

条件1指的是 起点
此处我们从 T.PARENTID=’-1’开始，找到的都是根节点

条件2指的是连接条件
此处我们用CONNECT BY PRIOR T.ID = T.PID
即上一条信息的ID是下一条信息的PID