START WITH . . . CONNECT BY . . .子句常用来实现SQL的层次查询.刚才看到一个蛮有趣的帖子,楼主用了数学方法深入研究了connect by
原帖子如下。
对于connect by,现在大多数人已经很熟悉了
connect by中的条件就表示了父子之间的连接关系
比如 connect by id=prior pid
但如果connect by中的条件没有表示记录之间的父子关系
那会出现什么情况?
常见的,connect by会在构造序列的时候使用
用select rownum from dual connect by rownum<xxx 代替早期版本的 select rownum from all_objects where rownum <xxx
我们注意到,dual是一个只有一条记录的表,如果表有多条记录,将会怎样?
下面开始实验
环境:windows xp sp2 + Oracle 9208
(10.1版本connect by有问题)
CREATE TABLE T
(
ID VARCHAR2(1 BYTE)
);
INSERT INTO T ( ID ) VALUES (
'A');
INSERT INTO T ( ID ) VALUES (
'B');
INSERT INTO T ( ID ) VALUES (
'C');
COMMIT;
SQL> select rownum from dual connect by rownum<10;
ROWNUM
----------
1
2
3
4
5
6
7
8
9
9 rows selected.
SQL> select id,level from t connect by level<2;
I LEVEL
- ----------
A 1
B 1
C 1
SQL> select id,level from t connect by level<3;
I LEVEL
- ----------
A 1
A 2
B 2
C 2
B 1
A 2
B 2
C 2
C 1
A 2
B 2
I LEVEL
- ----------
C 2
12 rows selected.
SQL> select id,level from t connect by level<4;
I LEVEL
- ----------
A 1
A 2
A 3
B 3
C 3
B 2
A 3
B 3
C 3
C 2
A 3
I LEVEL
- ----------
B 3
C 3
B 1
A 2
A 3
B 3
C 3
B 2
A 3
B 3
C 3
I LEVEL
- ----------
C 2
A 3
B 3
C 3
C 1
A 2
A 3
B 3
C 3
B 2
A 3
I LEVEL
- ----------
B 3
C 3
C 2
A 3
B 3
C 3
39 rows selected.
SQL>
无需多说,我们很快可以找到其中的规律,假设表中有N条记录
则记F(N,l)为 select id,level from t connect by level<l 的结果集数目
那么,
F(N,1)=N
F(N,l) = F(N,l-1)*N+N
于是可以总结出
F(N,l)=∑power(N,p), p取值为[1,l)
要解释,也很容易
当连接条件不能限制记录之间的关系时
每一条记录都可以作为自己或者其他记录的叶子
如下所示:
A 1
A 2
A 3
B 3
C 3
B 2
A 3
B 3
C 3
C 2
A 3
B 3
C 3
在这里,我们看到的是
Oracle采用了深度优先的算法
转摘自:http://www.cnblogs.com/zeromyth/archive/2009/09/07/1561948.html
分享到:
相关推荐
本文章详细介绍了Oracle中connect by...start with...的用法。
Oracle start with.connect by prior子句实现递归查询
NULL 博文链接:https://yunqiang-zhang-hotmail-com.iteye.com/blog/1312354
在Oracle中用Start with...Connect By子句递归查询
oracle中 connect by prior 递归算法 Oracle中start with...connect by prior子句用法 connect by 是结构化查询中用到的
Successfully design and simulate your 3D robot model and use powerful robotics algorithms and tools to program and set up your robots with an unparalleled experience by using the exciting new features...
主要给大家介绍了关于Oracle递归查询start with connect by prior、的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起学习学习吧
oracle中的数查询,介绍的详细,有例子。
Oracle row_number()over start with...connect by prior start with...connect by prior
Oracle_start_with_connect_by_prior_用法[文].pdf
文章主要知识点: Oracle 递归查询, start with connect by prior 用法 find_in_set 函数 concat,concat_ws,group_...第一种情况: start with 子节点id = ' 查询节点 ' connect by prior 子节点id = 父节点id
Source Code for Hello World with Pygame ................................................................................ 7 Setting Up a Pygame Program ....................................................
In a world where understanding big data has become key, by mastering R you will be able to deal with your data effectively and efficiently. This book will give you the guidance you need to build and...
A practical guide with clear instructions to design and develop a complete web application from start to finish Who This Book Is For This book is designed for JavaScript developers of any skill level ...
You'll start by creating, customizing, and extending HTTP-based web APIs and move on to host your web API with ASP.NET. Then, using a range of different features, you'll learn how to connect your ...
Oracle 数据库树形结构用法总结,例如SYS_CONNECT_BY_PATH 、START WITH . . . CONNECT BY . . .等具体语法介绍
ORACLE查询树型关系(connect_by_prior_start_with)
We start with the basics of networking and then explore how Java supports the development of client/server and peer-to-peer applications. The NIO packages are examined as well as multitasking and how ...
开发遇到一个报错 ORA-01436: CONNECT BY loop in user data (ORA-01436: 用户数据中的 CONNECT BY 循环)。 1. 报错原因 根据网上的资料,产生这个错误的原因是数据形成了循环。例如下面这个语句: SELECT r1....