Oracle递归查询，Oracle START WITH查询，Oracle树查询

Oracle递归查询，Oracle树查询，Oracle START WITH查询

 

================================

©Copyright 蕃薯耀 2018年5月14日

http://fanshuyao.iteye.com/

 

说明：

现在表（CMM_CODE）中有一批树结构的数据，主键为CODE_ID，以字段PARENT_CODE_ID作为父结构关联，最顶层结构的PARENT_CODE_ID为空，数据结构例子如下：

专业设施名称 > 公共服务设施 > 特殊设施

 现在要根据CODE_ID查询出完整的树结构名称，通过大于号（>）拼接起来，如上面所示。

 

一、方法一：使用START WITH……CONNECT BY查询，再使用SYS_CONNECT_BY_PATH拼接

SELECT CC.*,SUBSTR(SYS_CONNECT_BY_PATH(CC.CODE_NAME, '>'),2) TREE_PATH 
FROM CMM_CODE CC 
START WITH CC.Code_Id='pn_0501004' 
CONNECT BY PRIOR CC.PARENT_CODE_ID= CC.CODE_ID --此处PARENT_CODE_ID和CODE_ID顺序受PRIOR影响
;

 SYS_CONNECT_BY_PATH ： 和父结构名称拼接

SUBSTR ： 去除最前面的大于号（>）

CONNECT BY PRIOR ：需要注意的是PRIOR 的顺序

CONNECT BY PRIOR CC.PARENT_CODE_ID= CC.CODE_ID

 和

CONNECT BY CC.PARENT_CODE_ID= PRIOR  CC.CODE_ID

 查询出来的数据是不一样。

上面sql查询结果如下：

 

 查询出来的数据为3条是对的，但查询出来的数据不能再进行第二次的过滤查询，会报错，如：

SELECT * FROM (
    SELECT CC.*,SUBSTR(SYS_CONNECT_BY_PATH(CC.CODE_NAME,     '>'),2) TREE_PATH 
    FROM CMM_CODE CC 
    START WITH CC.Code_Id='pn_0501004' 
    CONNECT BY PRIOR CC.PARENT_CODE_ID= CC.CODE_ID--此处    PARENT_CODE_ID和CODE_ID顺序受PRIOR影响
) TEMP WHER TEMPCODE_ID='pn_0500000'
;

 

 

 

而且查询出来的结构顺序是相反的，如下：

特殊设施>公共服务设施>专业设施名称

 

方法二：使用WM_CONCAT函数进行拼接

--TO_CHAR是为了将clob转成一般字符类型
--REPLACE是将逗号（,）改成大于号（>）
--WM_CONCAT字符串拼接，默认是使用逗号（,）拼接
--查询结果：专业设施名称 > 公共服务设施 > 特殊设施
SELECT TO_CHAR(REPLACE(WM_CONCAT(TEMP.CODE_NAME), ',', ' > ')) TREE_PATH FROM(
  SELECT CC.* 
  FROM CMM_CODE CC 
  START WITH CC.Code_Id='pn_0501004' 
  CONNECT BY PRIOR CC.PARENT_CODE_ID=CC.CODE_ID
  ORDER BY CC.ORDER_NUMBER
)TEMP 
;

 查询结果如下：

专业设施名称 > 公共服务设施 > 特殊设施

 

结果顺序正确，但一样是不能关联其它的表进行查询。因为START WITH 后面必须加上具体的条件，不能使用关联的的字段。

 

方式三：START WITH关联查询

select info.*,
CC.CODE_NAME,SUBSTR(TEMP_TABLE.TREE_PATH, 4) TREE_PATH
from pn_place_name info 
LEFT JOIN CMM_CODE CC ON CC.CODE_ID=INFO.CMM_CODE_ID
LEFT JOIN (
     SELECT code.*,SYS_CONNECT_BY_PATH(code.CODE_NAME, ' > ') TREE_PATH 
    FROM CMM_CODE  code
    START WITH code.PARENT_CODE_ID IS NULL AND code.CLASS_CODE='PN_TYPE_NEW'
    CONNECT BY PRIOR code.CODE_ID= code.PARENT_CODE_ID
) TEMP_TABLE ON TEMP_TABLE.CODE_ID=INFO.CMM_CODE_ID
where 1=1 and info.ISINSPECTOPINION is null 
AND info.PN_NO='106737'
;

 SUBSTR(TEMP_TABLE.TREE_PATH, 4)：去掉最前面的 空格 + 大于号（>）+ 空格

 ' > '

 

方法三和方法二的区别在于START WITH 后面的条件，和

CONNECT BY PRIOR code.CODE_ID= code.PARENT_CODE_ID

的条件，此处的顺序是不一样的。

 

方法三是把所有PARENT_CODE_ID IS NULL 的数据都取出来，即把最顶层的结构都先取出来，再去关联子结构，取出来的数据是所有的。唯一的好处是可以关联其它表进行查询。

 

总结：

知道具体的id值，并且是单条数据查询，不需要关联表时，使用方式二。

需要关联表查询时，使用方式三。

 

（如果你觉得文章对你有帮助，欢迎捐赠，^_^，谢谢！） 

    

 

================================

©Copyright 蕃薯耀 2018年5月14日

http://fanshuyao.iteye.com/

评论

1 楼 蕃薯耀 2018-05-14  

Oracle递归查询
Oracle树查询
Oracle START WITH查询
Oracle START WITH……CONNECT BY查询
Oracle WM_CONCAT查询

=========
蕃薯耀