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/