`

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 路线便宜,所以用前者代替了后者。循环将在没有更便宜的费用,
并且没有其它可能路线时退出。
问题提出:

一个高级SQL语句问题
假设有一张表,A和B字段都是NUMBER,
A B
1 2
2 3
3 4
4
有这样一些数据
现在想用一条SQL语句,查询出这样的数据
1-》2-》3—》4
就是说,A和B的数据表示一种连接的关系,现在想通过A的一个值,去查询A所对应的B值,直到B为NULL为止,
不知道这个SQL语句怎么写?请教高手!谢谢

*********************************************************************************************/

--以下是利用start with connect by的一个简单回答

CREATE TABLE TEST(COL1 NUMBER(18,0),COL2 NUMBER(18,0));

INSERT INTO TEST VALUES(1,2);
INSERT INTO TEST VALUES(2,3);
INSERT INTO TEST VALUES(3,4);
INSERT INTO TEST VALUES(4,NULL);

INSERT INTO TEST VALUES(5,6);
INSERT INTO TEST VALUES(6,7);
INSERT INTO TEST VALUES(7,8);
INSERT INTO TEST VALUES(8,NULL);

INSERT INTO TEST VALUES(9,10);
INSERT INTO TEST VALUES(10,NULL);

INSERT INTO TEST VALUES(11,12);
INSERT INTO TEST VALUES(12,13);
INSERT INTO TEST VALUES(13,14);
INSERT INTO TEST VALUES(14,NULL);


select max(col) from(
select SUBSTR(col,1,CASE WHEN INSTR(col,'->')>0 THEN INSTR(col,'->') - 1 ELSE LENGTH(col) END) FLAG,col from(
select ltrim(sys_connect_by_path(col1,'->'),'->') col from (
select col1,col2,CASE WHEN LAG(COL2,1,NULL) OVER(ORDER BY ROWNUM) IS NULL THEN 1 ELSE 0 END FLAG
from test
)
start with flag=1 connect by col1=prior col2
)
)
group by flag
;
分享到:
评论

相关推荐

    树状数据库表:Oracle中start with...connect by prior子句用法

    本文将详细讲解如何利用`START WITH...CONNECT BY PRIOR`子句来构建和查询树形数据库表。 `START WITH...CONNECT BY PRIOR`是Oracle SQL中的一个特性,用于处理具有层级关系的数据。这个子句允许我们遍历和查询具有...

    Oracle中connect by...start with...的使用

    本文章详细介绍了Oracle中connect by...start with...的用法。

    Oracle递归查询start with connect by prior的用法

    connect by:connect by是必须的,start with有些情况是可以省略的,或者直接start with 1=1不加限制 prior:prior关键字可以放在等号的前面,也可以放在等号的后面,表示的意义是不一样的,比如 prior id = pid,...

    oracle数据库startwith用法

    通过本文,我们详细介绍了Oracle数据库中 `START WITH` 和 `CONNECT BY` 的使用方法以及应用场景。这两个关键字对于处理具有层级结构的数据非常有用。此外,我们还讨论了如何利用 `LEVEL` 关键字来显示节点所在的...

    connect by的使用探索

    [ WHERE condition ][ [ START WITH condition ] CONNECT BY condition [ ORDER SIBLINGS BY expression ] ] ``` 其中,`START WITH`子句用于指定查询的起始节点,`CONNECT BY`子句用于指定查询的递归条件。 ...

    Oracle_start_with_connect_by_prior_用法

    ### Oracle中的START WITH CONNECT BY PRIOR 用法详解 #### 一、概念介绍 在Oracle数据库中,`START WITH` 和 `CONNECT BY PRIOR` 是两个非常强大的特性,主要用于处理具有层级结构的数据。这两个特性可以帮助我们...

    Oracle_start_with_connect_by_prior_用法[文].pdf

    Oracle 连接查询是指使用 START WITH 和 CONNECT BY 语句来实现递归查询的方法,这种方法可以生成树形结构的数据。在 Oracle 中,START WITH 语句用于指定递归查询的开始记录,而 CONNECT BY 语句用于指定递归查询的...

    connect by的使用

    下面将详细阐述`CONNECT BY`的使用方法、原理以及一些常见的应用场景。 1. **基本语法** `CONNECT BY` 的基本语法如下: ```sql SELECT column1, column2, ... FROM table_name START WITH condition1 CONNECT BY ...

    MySQL多种递归查询方法.docx

    `START WITH CONNECT BY PRIOR`用法详解 **基本语法**: ```sql SELECT * FROM table_name START WITH condition CONNECT BY PRIOR child_column = parent_column; ``` 其中: - `START WITH`: 指定查询的起始条件...

    浅谈Oracle下connect by原理.pdf

    Oracle是甲骨文公司的一款关系型...使用Connect By进行查询时,需要特别注意循环关系的处理,以及正确地使用`START WITH`、`CONNECT BY`子句和相关伪列。掌握这些知识点,对于Oracle数据库开发人员来说是必不可少的。

    Oracle 数据库树形结构用法总结.mht

    Oracle 数据库树形结构用法总结,例如SYS_CONNECT_BY_PATH 、START WITH . . . CONNECT BY . . .等具体语法介绍

    oracle中connect-by-prior用法,实战解决日期分解问题.docx

    Oracle数据库中的`CONNECT BY PRIOR`是一个强大的查询构造器,用于处理树形数据结构,尤其在组织层级、部门结构或者员工管理系统中非常常见。这个功能允许我们从一个或多个根节点开始,按照指定的规则遍历整个树结构...

    Oracle树查询及相关函数

    5. **查找指定节点的所有直属及父级直属节点**:使用`START WITH...CONNECT BY`,但将`PRIOR`关键字放在`sjflid`后面,即`START WITH ID = some_id CONNECT BY PRIOR sjflid = ID`。这将返回所有直属父节点,顺序是...

    oracle connect by 和 分析函数总结.doc

    ### Oracle `CONNECT BY` 用法总结 `CONNECT BY` 是Oracle SQL中用于创建和遍历层次结构数据的语法。它允许你从一个或多个具有层级关系的表中提取出树形结构。以下是`CONNECT BY`的基本用法: 1. **树查询(递归...

    oracle树结构查询.DOC

    在实际应用中,`CONNECT BY`通常与`START WITH`一起使用,形成如下的SQL语句: ```sql SELECT * FROM emp START WITH EMPNO = 7839 CONNECT BY PRIOR EMPNO = MGR; ``` 这段SQL将返回所有以7839号员工为根的子树...

    数据库设计之递归树查询

    本文将深入探讨如何通过递归查询来解决这类问题,并着重讲解使用`WITH`语句来实现递归查询的方法,适用于多种数据库系统,如MySQL、PostgreSQL、SQL Server等。 一、理解递归查询 递归查询是一种在数据库中遍历层级...

    oracle中connect-by-prior用法,实战解决日期分解问题.pdf

    Oracle数据库中的`connect by prior`是一个强大的SQL查询构造,用于处理具有层级关系的数据,常见于组织结构、部门层级、时间序列分析等场景。这个特性允许我们遍历和查询树形结构,将层次数据平展为一行一列的形式...

    Oracle树查询总结

    Oracle 数据库在处理树形数据结构时,提供了一种强大的查询方法,即 `SELECT...START WITH...CONNECT BY...PRIOR` 语法。这种查询模式允许我们以递归方式遍历和检索具有层级关系的数据,例如组织结构、产品分类或者...

    将 CONNECT BY 移植到 DB2

    Oracle 使用 `CONNECT BY` 子句以及 `LEVEL`, `PRIOR`, `CONNECT_BY_ROOT` 等伪列来实现递归查询。相比之下,DB2 使用递归 CTE 和 `UNION ALL` 来实现相同的功能。 #### 动机 在将应用从 Oracle 迁移到 DB2 的过程...

Global site tag (gtag.js) - Google Analytics