`

connect by 例子(转)

阅读更多
原文地址:http://blog.163.com/termie_gongzhen/blog/static/1200243362009516114844293/?fromdm&fromSearch&isFromSearchEngine=

connect by 例子  2009-06-16 11:48:44|  分类: IT技术 |  标签: |举报 |字号大

小 订阅
层次查询子句connect by,用于构造层次结果集的查询。
语法:
[ START WITH condition ]
CONNECT BY [ NOCYCLE ] condition
说明:
a、START WITH:告诉系统以哪个节点作为根结点开始查找并构造结果集,该节点即为返回记录中的最高节点。
b、当分层查询中存在上下层互为父子节点的情况时,会返回ORA-01436错误。此时,需要在connect by后面加上NOCYCLE关键字。同时,可用connect_by_iscycle伪列定位出存在互为父子循环的具体节点。connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用,用法见示例2。

用法举例:

示例1:显示所有地名关系结构。
SQL> select * from t;
AREA_ID   AREA_NAME   MGR_ID
-------- ---------- ------
86       中国
01       北京       86
02       福建       86
0101     海淀区     01
0102     朝阳区     01
0103     东城区     01
0104     西城区     01
0201     厦门       02
0202     福州       02
020101   湖里       0201
020102   思明       0201
010401   复兴门     0104
010402   西单       0104
已选择13行。
SQL>
SQL> set pagesize 50
SQL> col AreaName for a12
SQL> col Root for a10
SQL> col Path for a24
SQL>
SQL> select rpad( ' ', 2*(level-1), ' ' ) || area_name "AreaName",
   2     connect_by_root area_name "Root",
   3     connect_by_isleaf "IsLeaf",
   4     level ,
   5     SYS_CONNECT_BY_PATH(area_name, '/') "Path"
   6   from t
   7   start with mgr_id is null
   8   connect by prior area_id = mgr_id;
AreaName     Root       IsLeaf       LEVEL Path
------------ ---------- ------ ---------- ------------------------
中国         中国             0           1 /中国
   北京       中国             0           2 /中国/北京
     海淀区   中国             1           3 /中国/北京/海淀区
     朝阳区   中国             1           3 /中国/北京/朝阳区
     东城区   中国             1           3 /中国/北京/东城区
     西城区   中国             0           3 /中国/北京/西城区
       复兴门 中国             1           4 /中国/北京/西城区/复兴门
       西单   中国             1           4 /中国/北京/西城区/西单
   福建       中国             0           2 /中国/福建
     厦门     中国             0           3 /中国/福建/厦门
       湖里   中国             1           4 /中国/福建/厦门/湖里
       思明   中国             1           4 /中国/福建/厦门/思明
     福州     中国             1           3 /中国/福建/福州
[size=large]已选择13行。
说明:
a、prior:是单一操作符,放在列名的前面,等号左右均可; 放在父 ID 就是 寻找 祖先节点 ,放到本身 ID就是寻找 子节点 ;
b、connect_by_root:是单一操作符,返回当前层的最顶层节点;
c、connect_by_isleaf:是伪列,判断当前层是否为叶子节点,1代表是,0代表否;
d、level:是伪列,显示当前节点层所处的层数;
e、SYS_CONNECT_BY_PATH:是函数,显示当前层的详细路径。  

示例2:找出人事部门中存在跟其他部门互为管理者的人员名单。
SQL> select * from t2;
EMP           DEPT   MGR
------------ ------ ----------
刘涛         总裁办
李飞         总裁办 刘涛
张强         总裁办 刘涛
王鹏         人事   李飞
李华         人事   李飞
张强         人事   李飞
李飞         行政   张强
吴华         行政   张强
已选择8行。
SQL>
SQL> col emp for a12
SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp"
   2   from t2
   3   start with dept ='人事'
   4   connect by prior emp = mgr;
ERROR:
ORA-01436: 用户数据中的 CONNECT BY 循环

未选定行

说明:张强和李飞互为管理者,因此,要用nocycle,如下所示:
SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp"
   2       from t2
   3       start with dept ='人事'
   4       connect by nocycle prior emp = mgr;
emp
------------
王鹏
李华
张强
   李飞
     王鹏
     李华
   吴华
已选择7行。

SQL>
SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp",
   2     connect_by_iscycle "IsCycle"
   3   from t2
   4   start with dept ='人事'
   5   connect by prior emp = mgr;
connect by prior emp = mgr
           *
第 5 行出现错误:
ORA-30930: CONNECT_BY_ISCYCLE 伪列要求 NOCYCLE 关键字

说明:在用connect_by_iscycle定位节点时,也要用nocycle关键字,如下所示:

SQL>
SQL> select rpad( ' ', 2*(level-1), ' ' ) || emp "emp",
   2     connect_by_iscycle "IsCycle"
   3   from t2
   4   start with dept ='人事'
   5   connect by nocycle prior emp = mgr;emp           IsCycle
------------ -------
王鹏               0
李华               0
张强               0
   李飞             1
     王鹏           0
     李华           0
   吴华             0
已选择7行。
SQL>

示例3:仅显示第二层(即level=2)省市名称。
SQL> select rpad( ' ', 2*(level-1), ' ' ) || area_name "AreaName"
   2   from t
   3   where level = 2
   4   start with mgr_id is null
   5   connect by prior area_id = mgr_id;
AreaName
------------
   北京
   福建

示例4:用connect by构造序列。
SQL>
SQL> select rownum rn
   2   from dual
   3   connect by rownum<=10;
         RN
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
         10
已选择10行。
SQL> select rownum*2 -1 rn
   2   from dual
   3   connect by rownum<=10;
         RN
----------
         1
         3
         5
         7
         9
         11
         13
         15
         17
         19
已选择10行。

-----------------------------------------
附:建表语句
create table t (area_id varchar2(6), area_name varchar2(10), mgr_id varchar2(6));
insert into t values('86', '中国', null);
insert into t values('01', '北京', '86');
insert into t values('02', '福建', '86');
insert into t values('0101', '海淀区', '01');
insert into t values('0102', '朝阳区', '01');
insert into t values('0103', '东城区', '01');
insert into t values('0104', '西城区', '01');
insert into t values('0201', '厦门', '02');
insert into t values('0202', '福州', '02');
insert into t values('020101', '湖里', '0201');
insert into t values('020102', '思明', '0201');
insert into t values('010401', '复兴门', '0104');
insert into t values('010402', '西单', '0104');
commit;

create table t2 (emp varchar2(10), dept varchar2(6), mgr varchar2(10));
insert into t2 values('刘涛', '总裁办', null);
insert into t2 values('李飞', '总裁办', '刘涛');
insert into t2 values('张强', '总裁办', '刘涛');
insert into t2 values('王鹏', '人事', '李飞');
insert into t2 values('李华', '人事', '李飞');
insert into t2 values('张强', '人事', '李飞');
insert into t2 values('李飞', '行政', '张强');
insert into t2 values('吴华', '行政', '张强');
commit;
[/[/size]size][/size]
分享到:
评论

相关推荐

    Oracle的Connect By使用示例

    ### Oracle的Connect By使用示例 #### 一、引言 在关系数据库系统中,进行树状或层次结构数据查询时经常会遇到复杂性问题。Oracle数据库通过提供`START WITH...CONNECT BY`语句来简化这类查询操作。此功能最早出现...

    oracle connect by level 应用

    在这个例子中,`SYS_CONNECT_BY_PATH`函数用斜杠(/)分隔了路径中的每一个员工名。 需要注意的是,`CONNECT BY`可能会导致循环引用或无限循环,如果数据存在环状引用(员工A的上级是员工B,而员工B的上级又回到了...

    connect by prior数据库树的应用

    本文将详细介绍如何利用 `Connect By Prior` 来构建和查询层次结构数据,并通过具体的例子来解释其工作原理。 #### 核心知识点详解 ### 1. `Connect By Prior` 的基本语法与含义 `Connect By Prior` 是一个用于...

    start connect by

    在例子中,`CONNECT BY PRIOR employee_id = manager_id` 表示当前行的`employee_id`等于前一行的`manager_id`,这样就建立了员工与其直接上级之间的连接。这个过程会持续进行,直到所有符合规则的层级都被遍历。 `...

    Oracle start with.connect by prior子句实现递归查询

    ### Oracle Start With.Connect By Prior 子句实现递归查询 #### 概述 在Oracle数据库中,`Start With.Connect By Prior`子句是执行递归查询的一种强大工具,主要用于处理层次结构数据。这类数据通常存在于组织...

    浅谈Oracle下connect by原理.pdf

    文章中给出了一个名为`t_test_connectby`的数据表例子,其中包含父子关系的节点数据。通过该表,我们可以更直观地了解Connect By的执行过程。 - 不指定起始条件时,使用`CONNECT BY`: ```sql SELECT * FROM t_test...

    oracle sys_connect_by_path 函数 结果集连接

    在这个例子中,`start with`子句指定了树的起点,`connect by prior`则根据`no`字段的值链接每一层的节点。 最后,通过`sys_connect_by_path(name, ',')`,将每个节点的`name`字段按照它们在树中的路径连接起来,以...

    oracle-tree-sql.rar_oracle

    Oracle提供了一种称为“连接查询”(Connect By)的特有语法,使得我们可以方便地对具有层级关系的数据进行检索。本资料"oracle-tree-sql.rar_oracle"主要探讨的就是如何在Oracle中使用SQL查询树形结构数据。 一、...

    oracle常用函数.rar

    在"connectby.sql"文件中,可能包含了使用`CONNECT BY`创建组织结构、展示家族树或者分析数据依赖关系的例子。基本语法如下: ```sql SELECT column_list FROM table_name START WITH condition CONNECT BY prior ...

    oracle树结构查询.DOC

    Oracle数据库系统支持对树状结构数据的查询,这种查询方式主要通过`START WITH`和`CONNECT BY`子句实现。这两个子句是Oracle特有的,用于处理层级关系的数据,例如组织结构、产品分类或者树形菜单等。理解并掌握这两...

    oracle递归查询的例子

    2. **CONNECT BY PRIOR 子句**:用于定义递归关系,即如何从上一级记录推导出下一级记录。 3. **LEVEL 关键字**:用于获取当前记录的层级编号,这对于控制递归深度非常有用。 #### 三、示例表的创建与数据插入 首先...

    数据库设计之递归树查询

    虽然Oracle数据库不直接支持`WITH RECURSIVE`,但它提供了`CONNECT BY`关键字来处理树形结构。以下是在Oracle中进行递归查询的例子: ```sql SELECT id, name, CONNECT_BY_ROOT id AS root_id FROM employees START...

    RxSwift垃圾袋的使用例子

    let subscription = timer.connect().disposed(by: disposeBag) ``` 在这个例子中,`Timer.publish`创建了一个可观察的定时器,然后将其连接订阅到disposeBag。当视图控制器被销毁,disposeBag也会被释放,从而取消...

    abb工业机器人二次开发C#例子

    In this example I show how you can save already discovered controllers by their so called Guid. These unique identifiers can then be used to connect to the controllers straight away without having to ...

    解析execle并导入数据库的例子

    在这个例子中,`open_workbook`函数打开指定路径的Excel文件,返回一个`Workbook`对象。`sheet_by_index`或`sheet_by_name`方法用于获取工作表,可以根据索引或名称选择要操作的工作表。 解析Excel数据时,可以遍历...

    oracl比较经典的例子说明

    - 示例创建数据库链:`CREATE DATABASE LINK dbl_spnew CONNECT TO spnew IDENTIFIED BY spnew USING 'jhhx';` - 远程插入数据:`INSERT INTO acc_nbr@dbl_spnew SELECT * FROM acc_nbr WHERE nxx_nbr='237' AND ...

    socket简单应用例子

    print(f"Connected by {addr}") message = client_socket.recv(1024).decode() print(f"Received: {message}") response = "Hello from Server!" client_socket.send(response.encode()) client_socket....

Global site tag (gtag.js) - Google Analytics