`
fjfj910
  • 浏览: 88505 次
  • 性别: Icon_minigender_1
  • 来自: 北京
文章分类
社区版块
存档分类
最新评论

oracle中 start with connect by 用法

    博客分类：
  • SQL
阅读更多

oracle 提供了start with connect by 语法结构可以实现递归查询。

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 条件表示子数据需要满足父数据的什么条件【要的时候有两种写法：connect by prior day_number=day_number-1 或 connect by day_number=prior day_number-1，前一种写法表示采用自上而下的搜索方式（先找父节点然后找子节点），后一种写法表示采用自下而上的搜索方式（先找叶子节点然后找父节点）】。

在下面的这个start with connect by 结构中，就表示 查找出了从1开始，并且day_number 逐渐+1 递增的，并且 msisdn 相同的那些个数据.

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

3.  执行计划
对于这个特殊的语法结构，我们来看看它的执行计划。
通过下面的执行计划，我们可以看出，对于简单的访问一个对象的递归查询，实际上oracle 要三次访问要查询的对象。因此，这一个告诉我们，在使用递归查询时，一定要谨慎，因为即使原表数据不多，但是三倍的访问喜爱来，代价也会很大。

SQL> explain plan for
  2
  2   select * from  test
  3    --where  bill_month='200803'
  4    start with day_number=1
  5    connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  6  ;

Explained

SQL> select *  from  table( dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation                 |  Name       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |       |       |       |
|*  1 |  CONNECT BY WITH FILTERING|             |       |       |       |
|*  2 |   FILTER                  |             |       |       |       |
|   3 |    TABLE ACCESS FULL      | TEST        |       |       |       |
|   4 |   NESTED LOOPS            |             |       |       |       |
|   5 |    BUFFER SORT            |             |       |       |       |
|   6 |     CONNECT BY PUMP       |             |       |       |       |
|*  7 |    TABLE ACCESS FULL      | TEST        |       |       |       |
|   8 |   TABLE ACCESS FULL       | TEST        |       |       |       |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TEST"."DAY_NUMBER"=1)
   2 - filter("TEST"."DAY_NUMBER"=1)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   7 - filter("TEST"."MSISDN"=NULL AND "TEST"."DAY_NUMBER"-1=NULL)
Note: rule based optimization

23 rows selected

SQL>


另外，发现了在含有其他条件的递归中，是先处理所有的递归查询，最后才用加入的条件过滤.
请看下面的例子。
和上面的执行计划对比下我们可以知道，加入条件   where  bill_month='200803' 后，实际上却是在递归完成后，最后才执行的    1 - filter("TEST"."BILL_MONTH"='200803') 。

所 以，为了确保语句的性能，不要直接加入条件在start with connect by 结构中，而是要想办法将原表的数据控制住。这个可以采用子查询的办法，或者使用临时表等（最好采用临时表，将数据量从本源上控制住；因为从子查询的执行计 划我们可以看到，它每次也都是访问全表，再用条件过滤，要重复三次，不是一次过滤就够了).

--直接加入条件后的执行计划
SQL> explain plan for
  2
  2   select * from  test
  3    where  bill_month='200803'
  4    start with day_number=1
  5    connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  6  ;

Explained

SQL> select *  from  table( dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
| Id  | Operation                  |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |             |       |       |       |
|*  1 |  FILTER                    |             |       |       |       |
|*  2 |   CONNECT BY WITH FILTERING|             |       |       |       |
|*  3 |    FILTER                  |             |       |       |       |
|   4 |     TABLE ACCESS FULL      | TEST        |       |       |       |
|   5 |    NESTED LOOPS            |             |       |       |       |
|   6 |     BUFFER SORT            |             |       |       |       |
|   7 |      CONNECT BY PUMP       |             |       |       |       |
|*  8 |     TABLE ACCESS FULL      | TEST        |       |       |       |
|   9 |    TABLE ACCESS FULL       | TEST        |       |       |       |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TEST"."BILL_MONTH"='200803')

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   2 - filter("TEST"."DAY_NUMBER"=1)
   3 - filter("TEST"."DAY_NUMBER"=1)
   8 - filter("TEST"."MSISDN"=NULL AND "TEST"."DAY_NUMBER"-1=NULL)
Note: rule based optimization

25 rows selected

SQL>


--使用子查询，将过滤条件嵌在子查询中
SQL> explain plan for
  2
  2  select * from (select * from test
  3        where  bill_month='200803')
  4       start with day_number=1
  5       connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  6      ;

Explained

SQL> select *  from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-------------------------------------------------------------------------
| Id  | Operation                 |  Name       | Rows  | Bytes | Cost  |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |             |       |       |       |
|*  1 |  CONNECT BY WITH FILTERING|             |       |       |       |
|*  2 |   FILTER                  |             |       |       |       |
|*  3 |    TABLE ACCESS FULL      | TEST        |       |       |       |
|   4 |   NESTED LOOPS            |             |       |       |       |
|   5 |    BUFFER SORT            |             |       |       |       |
|   6 |     CONNECT BY PUMP       |             |       |       |       |
|*  7 |    TABLE ACCESS FULL      | TEST        |       |       |       |
|*  8 |   TABLE ACCESS FULL       | TEST        |       |       |       |
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TEST"."DAY_NUMBER"=1)
   2 - filter("TEST"."DAY_NUMBER"=1)

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   3 - filter("TEST"."BILL_MONTH"='200803')
   7 - filter("TEST"."BILL_MONTH"='200803' AND "TEST"."MSISDN"=NULL AND
              "TEST"."DAY_NUMBER"-1=NULL)
   8 - filter("TEST"."BILL_MONTH"='200803')
Note: rule based optimization

26 rows selected

SQL>



4. 实际中 递归查询的使用。

问题：
数据库里有字段day_number，msisdn。如何写月度连续3天有记录的手机号？表结构如下：

id   bill_month   day_number     msisdn
1      200803      1           13800000000
2      200803      1           130137.....
3      200803      2           13800000000
4      200803      3           13800000000
..............................

表中3月份连续3天有记录的纪录就是1380000000。请问如何写这样的sql？


解决方案：
SQL> create  table   test ( bill_month varchar2(20),day_number number ,msisdn varchar2(20));

Table created

SQL> insert into  test values ( '200803',1,'13800');

1 row inserted

SQL> insert into  test values ( '200803',3,'13800');

1 row inserted

SQL> insert into  test values ( '200803',2,'13800');

1 row inserted

SQL> insert into  test values ( '200803',2,'13801');

1 row inserted

SQL> insert into  test values ( '200803',4,'13804');

1 row inserted

SQL> insert into  test values ( '200803',5,'13804');

1 row inserted

SQL> commit;

Commit complete

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

6 rows selected

SQL>
SQL> select distinct  msisdn  from test  a
  2  where  bill_month='200803'
  3  and exists
  4  ( select msisdn from  test
  5    where  bill_month='200803' and msisdn=a.msisdn
  6    start with day_number=a.day_number
  7    connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  8    group by msisdn
  9    having count(*)>=3
 10    );

MSISDN
--------------------
13800


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

6 rows selected

SQL> insert into  test values ( '200803',7,'13804');

1 row inserted

SQL> insert into  test values ( '200803',8,'13804');

1 row inserted

SQL> insert into  test values ( '200803',6,'13802');

1 row inserted

SQL> insert into  test values ( '200803',6,'13801');

1 row inserted

SQL> insert into  test values ( '200803',7,'13801');

1 row inserted

SQL> insert into  test values ( '200803',8,'13801');

1 row inserted

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> commit;

Commit complete

SQL>
SQL> select distinct  msisdn  from test  a
  2  where  bill_month='200803'
  3  and exists
  4  ( select msisdn from  test
  5    where  bill_month='200803' and msisdn=a.msisdn
  6    start with day_number=a.day_number
  7    connect by  prior day_number=day_number-1 and prior msisdn= msisdn
  8    group by msisdn
  9    having count(*)>=3
 10    );

MSISDN
--------------------
13800
13801

分享到：
| Date、String、Calendar类型之间的转化
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

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

    主要给大家介绍了关于Oracle递归查询start with connect by prior、的相关资料，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面来一起学习学习吧

    Oracle_start_with_connect_by_prior_用法

    oracle中的数查询，介绍的详细，有例子。

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

    NULL 博文链接：https://yunqiang-zhang-hotmail-com.iteye.com/blog/1312354

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

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

    Oracle_start_with_connect_by_prior_用法[文].pdf

    Oracle_start_with_connect_by_prior_用法[文].pdf

    MySQL多种递归查询方法.docx

    Oracle 递归查询， start with connect by prior 用法 find_in_set 函数 concat，concat_ws，group_concat 函数 MySQL 自定义函数 手动实现 MySQL 递归查询 Oracle 递归查询 在 Oracle 中是通过 start ...

    connect_by_prior_递归算法

    oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的

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

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

    最全的oracle常用命令大全.txt

    下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。 1、用户 查看当前用户的缺省表空间 SQL>select username,default_tablespace from user_users; 查看当前用户的角色 SQL>select * from user_...

    Oracle 主要配置文件介绍

    系统级的环境变量一般在/etc/profile 文件中定义 在 CAMS 系统 与数据库 相关的环境变量就定义在/etc/profile 文件中 如下所示 export ORACLE_BASE=/u01/app/oracle export ORACLE_HOME=$ORACLE_BASE/...

    韩顺平oracle学习笔记

    概述:在oracle中要创建一个新的用户使用create user语句，一般是具有dba(数据库管理员)的权限才能使用。 create user 用户名 identified by 密码; 2.给用户修改密码 概述:如果给自己修改密码可以直接使用 sql>...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    其一、就业面广：全球前100强企业99家都在使用ORACLE相关技术，中国政府机构，大中型企事业单位都能有ORACLE技术的工程师岗位。 其二、技术层次深：如果期望进入IT服务或者产品公司（类似毕博、DELL、IBM等），...

    Mysql树形递归查询的实现方法

    前言 对于数据库中的树形结构数据，如...oracle实现递归查询的话，就可以使用start with … connect by connect by递归查询基本语法是： select 1 from 表格 start with … connect by prior id = pId start with

    Oracle SQL树形结构查询

    本文介绍Oracle中使用START WITH...CONNECT BY PRIOR子句实现递归查询树形结构的方法，小伙伴们可以参考一下。

    Oracle事例

    20.oracle8中扩充了group by rollup和cube的操作。有时候省了你好多功夫的。 下面的语句可以进行总计 select region_code,count(*) from aicbs.acc_woff_notify group by rollup(region_code); <2> 对第1个字段...

    orcale常用命令

    下面按类别列出一些ORACLE用户常用数据字典的查询使用方法。 1、用户 查看当前用户的缺省表空间 SQL>select username,default_tablespace from user_users; 查看当前用户的角色 SQL>select * from user_...

    SQL21日自学通

    在一个SELECT 语句中使用多个表119 正确地找到列123 等值联合124 不等值联合129 外部联合与内部联合130 表的自我联合132 总结134 问与答134 校练场134 练习135 第七天子查询内嵌的SQL 子句136 目标136 建立一个子...

    SQL培训第一期

    1 SQL基础 1.1 基本概念 结构化查询语言(Structured Query ...select后面出现的列,如果没有使用集合函数,必须出现在group by 中。 select sno,sname,sum(grade) from student group by sno,sname; //合法写法 select...

    SQL性能优化

    1 性能优化 1.1 避免频繁 commit，尤其...说明：除非是单据的单号，要求必须是唯一，并且依据流水号不可以跳号，不然在大量交易的表格中，不在乎跳耗时，要取得唯一的Primary Key 建议使用Oracle Sequence这样速度会较...

Magicbox
Global site tag (gtag.js) - Google Analytics