现在的位置: 首页 > 综合 > 正文

sys_connect_by_path函數的用法

2013年10月14日 ⁄ 综合 ⁄ 共 12413字 ⁄ 字号 评论关闭
文章目录

 

Oraclesys_connect_by_path行转列的使用

这个函数使用之前必须先建立一个树,否则无用

sys_connect_by_path(字段名, 2个字段之间的连接符号),注意这里的连接符号不要使用逗号,oracle会报错,如果一定要用,可以使用replace替换一下,方法如下 REPLACE(字段名,原字符,',')。

树: start with 条件1 connect by prior 条件2

条件1如 子=XX (或父=XX)

条件2 若: 子-->根(倒树) 则为 connect by 子 = prior 根

若 根--> 子(正树) 则 connect 根 = prior 子

-- 子全部显示 根-->子   level代表级别
with
tmp_a as (
select '1' a,'0' p from dual
union all
select '2','1' from dual
union all
select '3','1' from dual
union all
select '4','3' from dual
union all
select '5','2' from dual
union all
select '6','5' from dual
)
select a,p,sys_connect_by_path(a,'--'),level from tmp_a
start with a = 1
connect by p = prior a

-- 2和2的所有下级去掉 根-->子 (开始就要去掉)
with
tmp_a as (
select '1' a,'0' p from dual
union all
select '2','1' from dual
union all
select '3','1' from dual
union all
select '4','3' from dual
union all
select '5','2' from dual
union all
select '6','5' from dual
)
select a,p,sys_connect_by_path(a,'--') from tmp_a
start with p = 1 and a <> '2'
connect by p = prior a

-- 2的所有下级都去掉 根-->子 (connect 时去掉)
with
tmp_a as (
select '1' a,'0' p from dual
union all
select '2','1' from dual
union all
select '3','1' from dual
union all
select '4','3' from dual
union all
select '5','2' from dual
union all
select '6','5' from dual
)
select a,p,sys_connect_by_path(a,'--') from tmp_a
start with a = 1
connect by p = prior a and p <> '2' --去掉2的分枝

-- 2的下一级去掉 根-->子 (where 中去掉)
with
tmp_a as (
select '1' a,'0' p from dual
union all
select '2','1' from dual
union all
select '3','1' from dual
union all
select '4','3' from dual
union all
select '5','2' from dual
union all
select '6','5' from dual
)
select a,p,sys_connect_by_path(a,'--') from tmp_a
where p <> '2' -- 2
start with a = 1
connect by p = prior a

--显示最长的 根-->子
with
tmp_tab as (
select '中国' s,null b from dual
union all
select '广东' s,'中国' b from dual
union all
select '湖南' s,'中国' b from dual
union all
select '衡阳' s,'湖南' b from dual
union all
select '广州' s,'广东' b from dual
union all
select '衡东' s,'衡阳' b from dual
)
select max(sys_connect_by_path(s,'/')) from tmp_tab
start with s = '湖南'
connect by prior s = b --

-- 构造树 start with rn1 = 1 connect by rn - 1 = prior rn
with
tmp_tab as
(
select '1' tno,'n1' flag from dual
union all
select '1' tno,'n2' flag from dual
union all
select '1' tno,'n3' flag from dual
union all
select '2' tno,'t1' flag from dual
union all
select '2' tno,'t3' flag from dual
union all
select '3' tno,'f1' flag from dual
union all
select '3' tno,'f2' flag from dual
union all
select '3' tno,'f3' flag from dual
union all
select '3' tno,'f4' flag from dual
)
select a.*,sys_connect_by_path(a.flag,'--') from (
select tno,flag,tno+row_number() over (order by tno) rn,
row_number() over (partition by tno order by tno) rn1
from tmp_tab ) a
start with rn1 = 1
connect by rn - 1 = prior rn

-- 构造树 ,并取每组最大值,并去掉最前面的 -- 符号 , 并把 -- 换成 , (可用ltrim或substr(str,3))
with
tmp_tab as
(
select '1' tno,'n1' flag from dual
union all
select '1' tno,'n2' flag from dual
union all
select '1' tno,'n3' flag from dual
union all
select '2' tno,'t1' flag from dual
union all
select '2' tno,'t3' flag from dual
union all
select '3' tno,'f1' flag from dual
union all
select '3' tno,'f2' flag from dual
union all
select '3' tno,'f3' flag from dual
union all
select '3' tno,'f4' flag from dual
)
select tno,replace(ltrim(max(sys_connect_by_path(a.flag,'--')),'--'),'--',',') from (
select tno,flag,

DENSE_RANK() OVER(ORDER BY tno)+row_number() over (order by tno) rn,
row_number() over (partition by tno order by tno) rn1
from tmp_tab ) a
start with rn1 = 1
connect by rn - 1 = prior rn
group by tno

 

SYS_CONNECT_BY_PATH 函数

SYS_CONNECT_BY_PATH 和几个伪列CONNECT_BY_ROOT,CONNECT_BY_LEAF,CONNECT_BY_ISCYCLE

SYS_CONNECT_BY_PATH 函数

自从Since Oracle 9i 开始,就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 如下例所示:
column path format a50
select level,sys_connect_by_path(child,"/") path
from hier
start with parent is null
connect by prior child = parent;

LEVEL PATH
-------- --------------------------------------------
1 /Asia
2 /Asia/China
3 /Asia/China/Beijing
2 /Asia/Japan
3 /Asia/Japan/Osaka
3 /Asia/Japan/Tokyo
1 /Australia
2 /Australia/New South Wales
3 /Australia/New South Wales/Sydney
1 /Europe
2 /Europe/United Kingdom
3 /Europe/United Kingdom/England
4 /Europe/United Kingdom/England/London
1 /North America
2 /North America/Canada
3 /North America/Canada/Ontario
4 /North America/Canada/Ontario/Ottawa
4 /North America/Canada/Ontario/Toronto
2 /North America/USA
3 /North America/USA/California
4 /North America/USA/California/Redwood Shores

在 Oracle 10g 中,还有其他更多关于层次查询的新特性 。例如,有的时候用户更关心的是每个层次分支中等级最低的内容。那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如 果是叶子就会在伪列中显示“1”,如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。下给出了一个关于这个函数使用的例子:

select connect_by_isleaf,sys_connect_by_path(child,"/") path
from hier
start with parent is null
connect by prior child = parent;

CONNECT_BY_ISLEAF PATH
---------------------------------- ------------
0 /Asia
0 /Asia/China
1 /Asia/China/Beijing
0 /Asia/Japan
1 /Asia/Japan/Osaka
1 /Asia/Japan/Tokyo
0 /Australia
0 /Australia/New South Wales
1 /Australia/New South Wales/Sydney
0 /Europe
0 /Europe/United Kingdom
0 /Europe/United Kingdom/England
1 /Europe/United Kingdom/England/London
0 /North America
0 /North America/Canada
0 /North America/Canada/Ontario
1 /North America/Canada/Ontario/Ottawa
1 /North America/Canada/Ontario/Toronto
0 /North America/USA
0 /North America/USA/California
1 /North America/USA/California/Redwood Shores

 

在Oracle 10g 中还有一个新操作——CONNECT_BY_ROOT。 它用在列名之前用于返回当前层的根节点。如下面的例子,我可以显示出层次结构表中当前行数据所对应的最高等级节点的内容。

select connect_by_root child,sys_connect_by_path(child,"/") path
from hier
start with parent is null
connect by prior child = parent;

CONNECT_BY_ROOT PATH
------------------------------ --------
Asia /Asia
Asia /Asia/China
Asia /Asia/China/Beijing
Asia /Asia/Japan
Asia /Asia/Japan/Osaka
Asia /Asia/Japan/Tokyo
Australia /Australia
Australia /Australia/New South Wales
Australia /Australia/New South Wales/Sydney
Europe /Europe
Europe /Europe/United Kingdom
Europe /Europe/United Kingdom/England
Europe /Europe/United Kingdom/England/London
North America /North America
North America /North America/Canada
North America /North America/Canada/Ontario
North America /North America/Canada/Ontario/Ottawa
North America /North America/Canada/Ontario/Toronto
North America /North America/USA
North America /North America/USA/California
North America /North America/USA/California/Redwood Shores

在Oracle 10g 之前的版本中,如果在你的树中出现了环状循环(如一个孩子节点引用一个父亲节点),Oracle 就会报出一个错误提示:“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。而在 Oracle 10g 中,只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE, 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环,那么该行的伪列中就会显示“1”,否则就显示“0”。如下例所示:

create table hier2
(
parent number,
child number
);

insert into hier2 values(null,1);
insert into hier2 values(1,2);
insert into hier2 values(2,3);
insert into hier2 values(3,1);

select connect_by_iscycle,sys_connect_by_path(child,"/") path
from hier2
start with parent is null
connect by nocycle prior child = parent;

CONNECT_BY_ISCYCLE PATH
------------------ -------
0 /1
0 /1/2
1 /1/2/3

 

文章转载自:http://blog.csdn.net/lky5387/article/details/4972310

Oraclesys_connect_by_path行转列的使用

这个函数使用之前必须先建立一个树,否则无用

sys_connect_by_path(字段名, 2个字段之间的连接符号),注意这里的连接符号不要使用逗号,oracle会报错,如果一定要用,可以使用replace替换一下,方法如下 REPLACE(字段名,原字符,',')。

树: start with 条件1 connect by prior 条件2

条件1如 子=XX (或父=XX)

条件2 若: 子-->根(倒树) 则为 connect by 子 = prior 根

若 根--> 子(正树) 则 connect 根 = prior 子

-- 子全部显示 根-->子   level代表级别
with
tmp_a as (
select '1' a,'0' p from dual
union all
select '2','1' from dual
union all
select '3','1' from dual
union all
select '4','3' from dual
union all
select '5','2' from dual
union all
select '6','5' from dual
)
select a,p,sys_connect_by_path(a,'--'),level from tmp_a
start with a = 1
connect by p = prior a

-- 2和2的所有下级去掉 根-->子 (开始就要去掉)
with
tmp_a as (
select '1' a,'0' p from dual
union all
select '2','1' from dual
union all
select '3','1' from dual
union all
select '4','3' from dual
union all
select '5','2' from dual
union all
select '6','5' from dual
)
select a,p,sys_connect_by_path(a,'--') from tmp_a
start with p = 1 and a <> '2'
connect by p = prior a

-- 2的所有下级都去掉 根-->子 (connect 时去掉)
with
tmp_a as (
select '1' a,'0' p from dual
union all
select '2','1' from dual
union all
select '3','1' from dual
union all
select '4','3' from dual
union all
select '5','2' from dual
union all
select '6','5' from dual
)
select a,p,sys_connect_by_path(a,'--') from tmp_a
start with a = 1
connect by p = prior a and p <> '2' --去掉2的分枝

-- 2的下一级去掉 根-->子 (where 中去掉)
with
tmp_a as (
select '1' a,'0' p from dual
union all
select '2','1' from dual
union all
select '3','1' from dual
union all
select '4','3' from dual
union all
select '5','2' from dual
union all
select '6','5' from dual
)
select a,p,sys_connect_by_path(a,'--') from tmp_a
where p <> '2' -- 2
start with a = 1
connect by p = prior a

--显示最长的 根-->子
with
tmp_tab as (
select '中国' s,null b from dual
union all
select '广东' s,'中国' b from dual
union all
select '湖南' s,'中国' b from dual
union all
select '衡阳' s,'湖南' b from dual
union all
select '广州' s,'广东' b from dual
union all
select '衡东' s,'衡阳' b from dual
)
select max(sys_connect_by_path(s,'/')) from tmp_tab
start with s = '湖南'
connect by prior s = b --

-- 构造树 start with rn1 = 1 connect by rn - 1 = prior rn
with
tmp_tab as
(
select '1' tno,'n1' flag from dual
union all
select '1' tno,'n2' flag from dual
union all
select '1' tno,'n3' flag from dual
union all
select '2' tno,'t1' flag from dual
union all
select '2' tno,'t3' flag from dual
union all
select '3' tno,'f1' flag from dual
union all
select '3' tno,'f2' flag from dual
union all
select '3' tno,'f3' flag from dual
union all
select '3' tno,'f4' flag from dual
)
select a.*,sys_connect_by_path(a.flag,'--') from (
select tno,flag,tno+row_number() over (order by tno) rn,
row_number() over (partition by tno order by tno) rn1
from tmp_tab ) a
start with rn1 = 1
connect by rn - 1 = prior rn

-- 构造树 ,并取每组最大值,并去掉最前面的 -- 符号 , 并把 -- 换成 , (可用ltrim或substr(str,3))
with
tmp_tab as
(
select '1' tno,'n1' flag from dual
union all
select '1' tno,'n2' flag from dual
union all
select '1' tno,'n3' flag from dual
union all
select '2' tno,'t1' flag from dual
union all
select '2' tno,'t3' flag from dual
union all
select '3' tno,'f1' flag from dual
union all
select '3' tno,'f2' flag from dual
union all
select '3' tno,'f3' flag from dual
union all
select '3' tno,'f4' flag from dual
)
select tno,replace(ltrim(max(sys_connect_by_path(a.flag,'--')),'--'),'--',',') from (
select tno,flag,

DENSE_RANK() OVER(ORDER BY tno)+row_number() over (order by tno) rn,
row_number() over (partition by tno order by tno) rn1
from tmp_tab ) a
start with rn1 = 1
connect by rn - 1 = prior rn
group by tno

 

SYS_CONNECT_BY_PATH 函数

SYS_CONNECT_BY_PATH 和几个伪列CONNECT_BY_ROOT,CONNECT_BY_LEAF,CONNECT_BY_ISCYCLE

SYS_CONNECT_BY_PATH 函数

自从Since Oracle 9i 开始,就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 如下例所示:
column path format a50
select level,sys_connect_by_path(child,"/") path
from hier
start with parent is null
connect by prior child = parent;

LEVEL PATH
-------- --------------------------------------------
1 /Asia
2 /Asia/China
3 /Asia/China/Beijing
2 /Asia/Japan
3 /Asia/Japan/Osaka
3 /Asia/Japan/Tokyo
1 /Australia
2 /Australia/New South Wales
3 /Australia/New South Wales/Sydney
1 /Europe
2 /Europe/United Kingdom
3 /Europe/United Kingdom/England
4 /Europe/United Kingdom/England/London
1 /North America
2 /North America/Canada
3 /North America/Canada/Ontario
4 /North America/Canada/Ontario/Ottawa
4 /North America/Canada/Ontario/Toronto
2 /North America/USA
3 /North America/USA/California
4 /North America/USA/California/Redwood Shores

在 Oracle 10g 中,还有其他更多关于层次查询的新特性 。例如,有的时候用户更关心的是每个层次分支中等级最低的内容。那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如 果是叶子就会在伪列中显示“1”,如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。下给出了一个关于这个函数使用的例子:

select connect_by_isleaf,sys_connect_by_path(child,"/") path
from hier
start with parent is null
connect by prior child = parent;

CONNECT_BY_ISLEAF PATH
---------------------------------- ------------
0 /Asia
0 /Asia/China
1 /Asia/China/Beijing
0 /Asia/Japan
1 /Asia/Japan/Osaka
1 /Asia/Japan/Tokyo
0 /Australia
0 /Australia/New South Wales
1 /Australia/New South Wales/Sydney
0 /Europe
0 /Europe/United Kingdom
0 /Europe/United Kingdom/England
1 /Europe/United Kingdom/England/London
0 /North America
0 /North America/Canada
0 /North America/Canada/Ontario
1 /North America/Canada/Ontario/Ottawa
1 /North America/Canada/Ontario/Toronto
0 /North America/USA
0 /North America/USA/California
1 /North America/USA/California/Redwood Shores

 

在Oracle 10g 中还有一个新操作——CONNECT_BY_ROOT。 它用在列名之前用于返回当前层的根节点。如下面的例子,我可以显示出层次结构表中当前行数据所对应的最高等级节点的内容。

select connect_by_root child,sys_connect_by_path(child,"/") path
from hier
start with parent is null
connect by prior child = parent;

CONNECT_BY_ROOT PATH
------------------------------ --------
Asia /Asia
Asia /Asia/China
Asia /Asia/China/Beijing
Asia /Asia/Japan
Asia /Asia/Japan/Osaka
Asia /Asia/Japan/Tokyo
Australia /Australia
Australia /Australia/New South Wales
Australia /Australia/New South Wales/Sydney
Europe /Europe
Europe /Europe/United Kingdom
Europe /Europe/United Kingdom/England
Europe /Europe/United Kingdom/England/London
North America /North America
North America /North America/Canada
North America /North America/Canada/Ontario
North America /North America/Canada/Ontario/Ottawa
North America /North America/Canada/Ontario/Toronto
North America /North America/USA
North America /North America/USA/California
North America /North America/USA/California/Redwood Shores

在Oracle 10g 之前的版本中,如果在你的树中出现了环状循环(如一个孩子节点引用一个父亲节点),Oracle 就会报出一个错误提示:“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。而在 Oracle 10g 中,只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE, 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环,那么该行的伪列中就会显示“1”,否则就显示“0”。如下例所示:

create table hier2
(
parent number,
child number
);

insert into hier2 values(null,1);
insert into hier2 values(1,2);
insert into hier2 values(2,3);
insert into hier2 values(3,1);

select connect_by_iscycle,sys_connect_by_path(child,"/") path
from hier2
start with parent is null
connect by nocycle prior child = parent;

CONNECT_BY_ISCYCLE PATH
------------------ -------
0 /1
0 /1/2
1 /1/2/3

 

文章转载自:http://blog.csdn.net/lky5387/article/details/4972310

抱歉!评论已关闭.