oracle中 sql语句：start with .. connect by prior.. 用法

前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。点击跳转到教程。

我们经常会将一个比较复杂的目录树存储到一个表中。或者将一些部门存储到一个表中，而这些部门互相有隶属关系。这个时候你就会用到connect by prior start with。oracle 提供了start with connect by 语法结构可以实现递归查询。

 

connect by 是结构化查询中用到的，其基本语法是：

　　select ... from tablename start with 条件1

　　connect by prior 条件2

　　where 条件3;

　　例：

　　select * from table

　　start with org_id = 'HBHqfWGWPy'

　　connect by prior org_id = parent_id;

　　简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段:

　　org_id,parent_id那么通过表示每一条记录的parent是谁，就可以形成一个树状结构。

　　用上述语法的查询可以取得这棵树的所有记录。

　　其中：

　　条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。

　　条件2 是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org_id = parent_id就是说上一条记录的org_id 是本条记录的parent_id，即本记录的父亲是上一条记录。

　　条件3 是过滤条件，用于对返回的所有记录进行过滤。

 

例如:

没有加中start with ... connect by prior ...的查询结果:

select t.dim_id, t.pid, level

from pmcode.pmcode_fj_tree_rl t

where t.dim_id in (select b.dim_id

                      from pmcode.PMCODE_KPI_DIM_OD b

                     where b.kpi_id = 'KC0011')

结果:

DIM_ID PID LEVEL

---------------------

1024 5003 0

1070 0 0

5003 1070 0

5006 0 0

------------------------------------------------------------------------------------

增加start with ... connect by prior ...以后的结果:

select t.dim_id, t.pid, level

from pmcode.pmcode_fj_tree_rl t

where t.dim_id in (select b.dim_id

                      from pmcode.PMCODE_KPI_DIM_OD b

                     where b.kpi_id = 'KC0011')

start with t.dim_id = '1070' ----表示从dim_id = '1070'开始(也就是说1070为根节点)

connect by prior t.dim_id = t.pid; ----表示上条记录的dim_id等于本条记录的pid

结果:

DIM_ID PID LEVEL

---------------------

1070 0 1

5003 1070 2

1024 5003 3

 

又如：

 

1. 一个简单举例:
SQL> select *  from test;

BILL_MONTH           DAY_NUMBER MSISDN
-------------------- ---------- --------------------
200803                        1 13800
200803                        3 13800
200803                        2 13800
200803                        2 13801
200803                        4 13804
200803                        5 13804
200803                        7 13804
200803                        8 13804
200803                        6 13802
200803                        6 13801
200803                        7 13801
200803                        8 13801

12 rows selected

SQL>
SQL> select * from test
  2       start with day_number=1
  3       connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  4      ;

BILL_MONTH           DAY_NUMBER MSISDN
-------------------- ---------- --------------------
200803                        1 13800
200803                        2 13800
200803                        3 13800

SQL>


上面的语句查找出了从1开始，并且day_number 逐渐+1 递增的，并且 msisdn 相同的哪些个数据.


2. start with  connect by 语法结构
 如上面说看到的 例子， 其语法结构为  start with condition  connect by  condition （含 prior 关键字)
start with conditon 给出的seed 数据的范围, connect by  后面给出了递归查询的条件,prior 关键字表示父数据，prior 条件表示子数据需要满足父数据的什么条件。

在下面的这个start with connect by 结构中，就表示 查找出了从1开始，父数据的day_number等于子数据的day_number-1而且父数据的msisdn=子数据的msisdn.

start with day_number=1
     connect by  prior day_number=day_number-1 and prior msisdn= msisdn