数据库学习笔记---connect by的简单用法（转载）

学习中碰到了connect ...by语法，特来学习，但发现不是那么简单。

先转过来，mark一下，或许日后还会回来看看。

原文链接：http://blog.csdn.net/lidew521/article/details/15471709

原文标题：数据库学习笔记---connect by的简单用法

connect by的简单用法
 技术qq交流群：JavaDream：251572072
  教程下载,在线交流:创梦IT社区:www.credream.com
  ------------------------------------------------------------
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   2        

    code example1：        

     select  TBL_TEST.*, level  from  TBL_TEST

    start  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    2 

    code example2：  
  
     select  TBL_TEST.*, level  from  TBL_TEST

    start  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-condition

level
 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 Shores 

CONNECT_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 Shores 

 CONNECT_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 Shores 

CONNECT_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 
 ----------------------------------------------