本文共 14289 字,大约阅读时间需要 47 分钟。
connect by的简单用法
技术qq交流群:JavaDream:251572072 教程下载,在线交流:创梦IT社区:_______以前的网站现在不弄了,抱歉 ------------------------------------------------------------ 1. 一、首先从一个经典的查询序列数例子入手:select level from dual connect by level <= 10
select level from dual connect by 1 = 1
上例均可查询得到1 .. N 的序列(但最多100行)
我们来分析一下其工作原理,level<=10用来控制循环的次数,即要重复多少次扫描表dual中的内容。第一次扫描得出的结果集的level都是1,第二次扫描的结果集的level都是2,依此类推。可能用文字描述的不太容易懂,下面我们通过试验来说明:
with x as( select 'aa' chr from dual
union all
select 'bb' chr from dual)
select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other from x connect by level <= 3
LEVEL CHR OTHER 1 aa aa 2 aa ---- aa 3 aa --------- aa 3 bb --------- bb 2 bb ---- bb 3 aa --------- aa 3 bb --------- bb 1 bb bb 2 aa ---- aa 3 aa --------- aa 3 bb --------- bb 2 bb ---- bb 3 aa --------- aa 3 bb --------- bb 可见是全部level的树形结构,当扫描对象是dual时,即一个level只生成一条记录.
二、如何解决from dual只显示100行的问题:
selectlevel from dual connectbylevel <=300
只显示100行,但据说只是9i的显示问题,解决方法如下:
select * from (level from dual connectbylevel <=300)
即可显示300行!用以解决无法多行显示问题
应用举例如下:
select to_date( 2008 || '0101' , 'yyyymmdd' ) + rownum - 1 rq,
to_char(to_date( 2008 || '0101' , 'yyyymmdd' ) + rownum - 1 , 'day' ) day
from ( select rownum from dual
connect by rownum <= to_date( 2008 || '1231' , 'yyyymmdd' ) - to_date( 2008 || '0101' , 'yyyymmdd' )+ 1 );
作用:列出所有日期及星期几,可用于查询工作日
三、start with ... connect by 用法讲解:
构建如下table:
ID NAME PID
1 10 0 2 11 1 3 20 0 4 12 1 5 121 2code example1:
select TBL_TEST.*, level from TBL_TESTstart with pid= 1 -- 可写到 connect by 后面
connect by prior pid = id
ID NAME PID LEVEL
2 11 1 1 1 10 0 2 4 12 1 1 1 10 0 2code example2:
select TBL_TEST.*, level from TBL_TESTstart with id = 5 -- 可写到 connect by 后面
connect by prior pid = id
ID NAME PID LEVEL
5 121 2 1 2 11 1 2 1 10 0 3说明:
1、先从start with pid=1 句开始查询 得到 2 11 1 1 =====> level置1;
2、根据pid = id,查询 id=1 句,得到 1 10 0 2 =====> level置2;
3、根据pid = id,查询 id=0 句,未查询到后结束该树枝;
注:prior pid = id 句说明 pid是id的父节点,通过pid查询id
四、sys_connect_by_path函数讲解:
sys_connect_by_path函数主要作用是可以把一个父节点下的所有子节点通过某个字符进行区分,在一个格中显示
select level ,sys_connect_by_path( id , '/' ) from TBL_TEST
start with pid= 1
connect by prior pid = id ;
select level ,sys_connect_by_path(pid, '/' ) from TBL_TEST
start with pid= 1
connect by prior pid = id ;
可以比较这两段代码的运行结果与code example1的结果之间的差异,即可理解此函数用法
或具体看下面的例子中对sys_connect_by_path的使用
==============================================================================================
附网上的一些资料,转自 yangtingkun start with ... connect by用法简介 sql有向图问题期待新解决方案
/*******************************************************************************
通过START WITH . . . CONNECT BY . . .子句来实现SQL的层次查询.
自从Oracle 9i开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。自从Oracle 10g 中,还有其他更多关于层次查询的新特性 。例如,有的时候用户更关心的是每个层次分支中等级最低的内容。
那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”, 如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。在Oracle 10g 之前的版本中,如果在你的树中出现了环状循环(如一个孩子节点引用一个父亲节点),
Oracle 就会报出一个错误提示:“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。 而在 Oracle 10g 中,只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE, 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环,那么该行的伪列中就会显示“1”,否则就显示“0”。The start with .. connect by clause can be used to select data that has a hierarchical relationship
(usually some sort of parent->child, boss->employee or thing->parts). It is also being used when an sql execution plan is explained.syntax:
select ... [start with initial-condition] connect by [nocycle] recurse-conditionlevel
With level it is possible to show the level in the hierarchical relation of all the data.--oracle 9i
sys_connect_by_path With sys_connect_by_path it is possible to show the entire path from the top level down to the 'actual' child.--oracle 10g
connect_by_root connect_by_root is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries. connect_by_is_leaf connect_by_isleaf is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries. connect_by_iscycle connect_by_is_cycle is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.--start with ... connect by ... 的处理机制
How must a start with ... connect by select statement be read and interpreted? If Oracle encounters such an SQL statement, it proceeds as described in the following pseude code.for rec in (select * from some_table) loop
if FULLFILLS_START_WITH_CONDITION(rec) then RECURSE(rec, rec.child); end if; end loop;procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is
begin APPEND_RESULT_LIST(rec); for rec_recurse in (select * from some_table) loop if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then RECURSE(rec_recurse,rec_recurse.child); end if; end loop; end procedure RECURSE;created by zhouwf0726 2006.
*******************************************************************************/
--创建测试表,增加测试数据
create table test(superid varchar2(20),id varchar2(20));
insert into test values('0','1');
insert into test values('0','2');insert into test values('1','11');
insert into test values('1','12');insert into test values('2','21');
insert into test values('2','22');insert into test values('11','111');
insert into test values('11','112');insert into test values('12','121');
insert into test values('12','122');insert into test values('21','211');
insert into test values('21','212');insert into test values('22','221');
insert into test values('22','222');commit;
--层次查询示例
select level||'层',lpad(' ',level*5)||id id from test start with superid = '0' connect by prior id=superid;select level||'层',connect_by_isleaf,lpad(' ',level*5)||id id
from test start with superid = '0' connect by prior id=superid;--给出两个以前在"数据库字符串分组相加之四"中的例子来理解start with ... connect by ...
--功能:实现按照superid分组,把id用";"连接起来 --实现:以下两个例子都是通过构造2个伪列来实现connect by连接的。/*------method one------*/
select superid,ltrim(max(sys_connect_by_path(id,';')),';') from( select superid,id,row_number() over(partition by superid order by superid) id1, row_number() over(order by superid) + dense_rank() over(order by superid) id2 from test ) start with id1=1 connect by prior id2 = id2 -1 group by superid order by superid;/*------method two------*/
select distinct superid,ltrim(first_value(id) over(partition by superid order by l desc),';') from( select superid,level l,sys_connect_by_path(id,';') id from( select superid,id,superid||rownum parent_rn,superid||to_char(rownum-1) rn from test ) connect by prior parent_rn = rn );--下面的例子实现把一个整数的各个位上的数字相加,通过这个例子我们再次理解connect by.
create or replace function f_digit_add(innum integer) return number
is outnum integer; begin if innum<0 then return 0; end if; select sum(nm) into outnum from( select substr(innum,rownum,1) nm from dual connect by rownum<length(innum) ); return outnum; end f_digit_add; /select f_digit_add(123456) from dual;
/********************************************************************************** *********************************************************************************** 下面是关于SQL解决有向图问题,在这个例子中作者提到的错误 select * from fares connect by prior arrive = depart start with depart = 'LHR'; ERROR: ORA-01436: CONNECT BY loop in user data 在oracle10g以上版本可以利用connect by的nocycle参数来解。有兴趣的朋友研究用一条sql实现有向图问题! *********************************************************************************** **********************************************************************************/一个常见的高级计算机科学问题可以在“有向图”的范畴之下描述。有向图是由一组向量和边所连接的一组有限的节点。
例如,一个节点可以想象为一座“城市”,而每个向量可以想象为两座城市间的一个“航线”。 有很多算法和论文讲到如何解决每种可能路线的遍历问题以及寻找最短路径或者最小代价路径的问题。 这些算法中大部分都是过程化的,或者是使用递归方面来解决的。然而 SQL 的声明性语言使得解决复杂的有向图问题更加容易, 而且不需要很多代码。让我们以两座城市之间的航线为例子,创建一个表保存一些假想数据:
create table airports
( code char(3) constraint airports_pk primary key, description varchar2(200) );insert into airports values ('LHR','London Heathrow, UK');
insert into airports values ('JFK','New York-Kennedy, USA'); insert into airports values ('GRU','Sao Paulo, Brazil');create table fares
( depart char(3), arrive char(3), price number, constraint fares_pk primary key (depart,arrive), constraint fares_depart_fk foreign key (depart) references airports, constraint fares_arrive_fk foreign key (arrive) references airports );insert into fares values('LHR','JFK',700);
insert into fares values('JFK','GRU',600); insert into fares values('LHR','GRU',1500); insert into fares values('GRU','LHR',1600);不能使用CONNECT BY 语法来解决如何从伦敦到圣保罗,因为在图中有数据产生一个环(从圣保罗飞回):
select * from fares connect by prior arrive = depart start with depart = 'LHR';
ERROR: ORA-01436: CONNECT BY loop in user data要解决有向图问题,我们需要创建一个临时表来保存两个节点之间所有可能的路径。我们必须注意不复制已经处理过的路径,
而且在这种情况下,我们不想路径走回开始处的同一个地点。我还希望跟踪到达目的地所需航程的数目,以及所走路线的描述。临时表使用以下脚本创建:
create global temporary table faretemp
( depart char(3), arrive char(3), hops integer, route varchar2(30), price number, constraint faretemp_pk primary key (depart,arrive) );一个简单的视图可以在稍微简化这个例子中使用的代码。视图可以根据 fares 表中的单个航程计算从 faretemp 表中的一个路径
到达一下一个航程的数据:create or replace view nexthop
as select src.depart, dst.arrive, src.hops+1 hops, src.route||','||dst.arrive route, src.price + dst.price price from faretemp src,fares dst where src.arrive = dst.depart and dst.arrive != src.depart; / show errors;这个算法相当简单。首先,使用 fares 表中的数据填充 faretemp 表,作为初始的航程。然后,取到我们刚才插入的所有数据,
使用它们建立所有可能的二航程(two-hop)路径。重复这一过程,直至在两个节点之间创建了新路径。 循环过程将在节点间所有可能的路径都被描述之后退出。如果我们只对某个开始条件感兴趣, 那么我们还可以限制第一次的插入从而减少装载数据的量。下面是发现路径的代码:truncate table faretemp;
begin -- initial connections insert into faretemp select depart,arrive,1,depart||','||arrive,price from fares; while sql%rowcount > 0 loop insert into faretemp select depart,arrive,hops,route,price from nexthop where (depart,arrive) not in (select depart,arrive from faretemp); end loop; end; / show errors; select * from faretemp order by depart,arrive;可以在表 A 中查看输出。
前面的数据有一个小问题。数据是点之间最短路径(最小航程数)的集合。然而,从伦敦到圣保罗的航程却不是最便宜的一个。
要解决最便宜的费用问题,需要对我们的循环做一个改进,当在一个航程中发现一个更便宜的路线时使用这个路线代替原来的路线。
修改后的代码如下:truncate table faretemp;
declare l_count integer; begin -- initial connections insert into faretemp select depart,arrive,1,depart||','||arrive,price from fares; l_count := sql%rowcount; while l_count > 0 loop update faretemp set (hops,route,price) = (select hops,route,price from nexthop where depart = faretemp.depart and arrive = faretemp.arrive) where (depart,arrive) in (select depart,arrive from nexthop where price < faretemp.price); l_count := sql%rowcount; insert into faretemp select depart,arrive,hops,route,price from nexthop where (depart,arrive) not in (select depart,arrive from faretemp); l_count := l_count + sql%rowcount; end loop; end; / show errors; select * from faretemp order by depart,arrive; 可能在表 B中查看输出。 算法发现LHR、JFK、GRU 路线比 LHR、GRU 路线便宜,所以用前者代替了后者。循环将在没有更便宜的费用, 并且没有其它可能路线时退出。 ------------------------------------------------------------------------------------------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 ShoresCONNECT_BY_ISLEAF伪列
在 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 ShoresCONNECT_BY_ROOT伪列
在Oracle 10g 中还有一个新操作——CONNECT_BY_ROOT。 它用在列名之前用于返回当前层的根节点。如下面的例子,我可以显示出层次结构表中当前行数据所对应的最高等级节点的内容。
select connect_by_root ,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 ShoresCONNECT_BY_ISCYCLE伪列
在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 ----------------------------------------------
转载地址:https://credream.blog.csdn.net/article/details/15471709 如侵犯您的版权,请留言回复原文章的地址,我们会给您删除此文章,给您带来不便请您谅解!