SYS_CONNECT_BY_PATH(column_name,'分隔符')函数
第一个参数是形成树形式的字段,第二个参数是父级和其子级分隔显示用的分隔符
伪列CONNECT_BY_ROOT,CONNECT_BY_LEAF,CONNECT_BY_ISCYCLE
示例1:查询表中树结构并转化为文本展示(转载)
SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(ou_code, ';')), ';') AS RESULT
FROM (SELECT category_id, ou_code, RN, LEAD(RN) OVER(ORDER BY RN) RN1
FROM (SELECT category_id,
ou_code,
ROW_NUMBER() OVER(ORDER BY category_id, ou_code DESC) RN
FROM (SELECT t.category_id, t.ou_code
FROM bpm_dcs_category_ou t
WHERE t.category_id=1000085)))
START WITH RN1 IS NULL
AND category_id = 1000085
CONNECT BY RN1 = PRIOR RN;
示例2:列转行(转载)
view plaincopy to clipboardprint
select max(substr(sys_connect_by_path(column_name,','),2))
from (select column_name,rownum rn from user_tab_columns where table_name ='DEPT')
start with rn=1 connect by rn=rownum ;
MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,','),2))
--------------------------------------------------------------------------------
DEPTNO,DEPTNAME,MGRNO
MAX(SUBSTR(SYS_CONNECT_BY_PATH(COLUMN_NAME,';'),2)) 先连接所有节点,从第二个字符开始截取,取最完整条目
其作用于LTRIM(MAX(SYS_CONNECT_BY_PATH(COLUMN_NAME,';')),';')(先连接所有节点,取最完整条目,删除左侧字符‘;’)类似
补充:结构化查询
START WITH ...CONNNECT BY PRIOR基本语法是:
SELECT ...FROM
START WITH (根节点,可以指定多个节点)
CONNECT BY PRIOR= (连接条件,PRIOR置于等号前,则从根节点到叶节点开始检索;置于等号后,则从叶节点到根节点开始检索)
WHERE (过滤返回记录,仅过滤被限定节点,其根节点和子节点均不受影响)
该查询访问路径如下:
从根节点开始,向下扫描子节点,该子节点已被访问则转向其最左侧未被访问的子节点,否则判断该节点是否为根节点,是则访问完毕,否则返回父节点重新执行判断。
即扫描整表判断所有节点树结构(遍历树结构)。