最基本的connect by的用法:

需求1: 我需要下钻所有的树得到level和parent_name
create table test_lvl1 (id number, parent_id number, name varchar2(10));
insert into test_lvl1 values (1,null,'SLI1');
insert into test_lvl1 values (2,1,'SLI2');
insert into test_lvl1 values (3,1,'SLI3');
insert into test_lvl1 values (4,null,'SLI4');
insert into test_lvl1 values (5,2,'SLI5');
insert into test_lvl1 values (6,3,'SLI6');
insert into test_lvl1 values (7,5,'SLI7');
insert into test_lvl1 values (8,7,'SLI8');
insert into test_lvl1 values (9,4,'SLI9');
insert into test_lvl1 values (10,3,'SLI10');
insert into test_lvl1 values (11,1,'SLI11');

kl@k01> select * from test_lvl1;

ID PARENT_ID NAME
---------- ---------- ----------
1 SLI1
2 1 SLI2
3 1 SLI3
4 SLI4
5 2 SLI5
6 3 SLI6
7 5 SLI7
8 7 SLI8
9 4 SLI9
10 3 SLI10
11 1 SLI11

11 rows selected.

select a.*, b.name parent_name
from
(select name, ID, PARENT_ID, LEVEL
from test_lvl1
start with parent_id is null
connect by prior id=PARENT_ID) a,
test_lvl1 b
where a.parent_id=b.id(+)

NAME ID PARENT_ID LEVEL PARENT_NAM
---------- ---------- ---------- ---------- ----------
SLI11 11 1 2 SLI1
SLI3 3 1 2 SLI1
SLI2 2 1 2 SLI1
SLI5 5 2 3 SLI2
SLI10 10 3 3 SLI3
SLI6 6 3 3 SLI3
SLI9 9 4 2 SLI4
SLI7 7 5 4 SLI5
SLI8 8 7 5 SLI7
SLI4 4 1
SLI1 1 1

11 rows selected.

* 解释一下:
* 1. Start with表示从那一层开始的，后面跟表达式,如: pid=0,
寻找继承关系时，指定的顶点，如果需要对整个表进行整理，比较常用
的作法是: PID is null. 例如这种情况顶点的Parent_ID显然是NULL,
所以从PID is null开始无疑是最完整的。验证如下:
* 2. prior 表示返回所以符合这种条件(如id=pid)的connect by操作结果.
即以循环的显示所有的记录和继承关系。可以试一下如果把prior去掉，那么
只有两条记录,验证如下:

select name, ID, PARENT_ID, LEVEL
from test_lvl1
start with parent_id is null
connect by prior id=PARENT_ID;

--- 如果用Chile ID或者其他列作为开始列，会丢掉分支, 如下查询，可以自己验证
select name, ID, PARENT_ID, LEVEL
from test_lvl1
start with id =1
connect by prior id=PARENT_ID(+);

select name, ID, PARENT_ID, LEVEL
from test_lvl1
start with NAME='SLI1'
connect by prior id(+)=PARENT_ID;

* 另外一个很有趣的现象，对上述start with条件不同，而且结果记录数也不同的查询查看执行计划
竟是完全相同的。测试版本是Oracle 10.2.0.1
测试过程如下，节约篇幅，结果不述:
1. Set autotrace on;
2. 执行上面的query,查看返回值和执行计划;

需求2，我需要每个从根节点到当前孩子节点的路径:
create table test_parent1 (id number, name varchar2(20), pid number, parent_name varchar2(20));
insert into test_parent1 values (1,'JEFF',null,null);
insert into test_parent1 values (2,'LARRY',1,'JEFF');
insert into test_parent1 values (3,'BILL',1,'JEFF');
insert into test_parent1 values (4,'DELL',1,'JEFF');
insert into test_parent1 values (5,'MARK',3,'LARRY');
insert into test_parent1 values (6,'KART',5,'MARK');
insert into test_parent1 values (7,'ANDY',6,'KART');
insert into test_parent1 values (8,'CANDY',null,null);
insert into test_parent1 values (9,'LOUIS',8,'CANDY');
commit;

--- 得到如下表:
kl@k01> select * from test_parent1;

ID NAME PID PARENT_NAME
---------- -------------------- ---------- --------------------
1 JEFF
2 LARRY 1 JEFF
3 BILL 1 JEFF
8 CANDY
4 DELL 1 JEFF
5 MARK 3 LARRY
6 KART 5 MARK
7 ANDY 6 KART
9 LOUIS 8 CANDY

8 rows selected.

可以看出来一个简单的层次关系，如果我希望得到每个人的直接主管，应该就足够了，
但是要得到每个人的顶级BOSS，即变成如下:
1 JEFF
2 LARRY 1
3 BILL 1
8 CANDY
4 DELL 1
5 MARK 1
6 KART 1
7 ANDY 1
9 LOUIS 8

我们还需要做如下处理,使用SYS_CONNECT_BY_PATH,这个函数只使用于9i和9i以上的版本，
她可以帮我返回一个字符串，这个字符串是一个from-root-to-node的字串。如下所示:
col PID_PATH for a20
col parent_name for a30
select ID, NAME, SYS_CONNECT_BY_PATH(pid,'/') as pid_path, SYS_CONNECT_BY_PATH(parent_name,'/') as parent_name
from test_parent1
start with pid=1 connect by prior id=pid;

ID NAME PID_PATH PARENT_NAME
---------- -------------------- -------------------- ------------------------------
2 LARRY /1 /JEFF
3 BILL /1 /JEFF
5 MARK /1/3 /JEFF/LARRY
6 KART /1/3/5 /JEFF/LARRY/MARK
7 ANDY /1/3/5/6 /JEFF/LARRY/MARK/KART
4 DELL /1 /JEFF

6 rows selected.

需求3, 字符串分组方法(这里参考了zhouwf0726在ITPUB里面的研究结果 http://www.itpub.net/thread-614563-1-1.html):
++++++++++++ Input ++++++++++++++++++++
ID MC
---------- ---------
1 11111
1 22222
2 11111
2 22222
3 11111
3 22222
3 33333

++++++++++++ output ++++++++++++++++++++
ID ADD_MC
---------- -------------------
1 11111;22222
2 11111;22222
3 11111;22222;33333
++++++++++++++++++++++++++++++++++++++++
实现方法和试验步骤如下:
drop table test purge;
create table test(id varchar2(10),mc varchar2(50));
insert into test values('1','11111');
insert into test values('1','22222');
insert into test values('2','11111');
insert into test values('2','22222');
insert into test values('3','11111');
insert into test values('3','22222');
insert into test values('3','33333');

commit;
* 先看看怎么利用connect by, 如果要利用connect by，必须考虑使用sys_connect_by_path(上面有详细介绍)
关键是如何选择connect by的条件,为什么要制定rn_id，我们需要对ID进行分组，从而把同组的MC组合起来，
而对于RN,
col MC for a10
select id, mc, row_number() over(partition by id order by id) rn_id,
row_number() over(order by id)+id rn
from test;

ID MC RN_ID RN
---------- ---------- ---------- ----------
1 11111 1 2
1 22222 2 3
2 11111 1 5
2 22222 2 6
3 11111 1 8
3 22222 2 9
3 33333 3 10

7 rows selected.

col add_mc for a50
select id,ltrim(max(sys_connect_by_path(mc,';')),';') add_mc from (
select id, mc, row_number() over(partition by id order by id) rn_id,
row_number() over(order by id)+id rn from test)
start with rn_id=1 connect by rn-1=prior rn
group by id
order by id
/

ID ADD_MC
---------- --------------------------------------------------
1 11111;22222
2 11111;22222
3 11111;22222;33333

3 rows selected.
## 解释: 首先要熟悉分析函数 row_number() over(partition by id order by id)
表示以id进行分组，显示每条记录中id的数量，order by id是对id进行排序。
rn是顺序的，如果直接connect by一组顺序的数，将一直连续下去，所以因为一直满足rn - 1= rn,
所以要使rn为 row_number() over(order by id)+id，因为每组的id都不一样，这样才能避免连续的数字出现。

需求4，我需要在输出的时候就对结果进行格式化:

lpad和level配合决定错行缩进显示:

select level||'layer' layer,lpad(' ',level*5)||id id
from test_parent1
start with pid=1 connect by prior id=pid;

LAYER ID
-------------------- -----------------------------------
1layer 2
1layer 3
2layer 5
3layer 6
4layer 7
1layer 4

6 rows selected.

需求4, 如果需要构建一种循环处理机制，比如把一个字符的每个字母输出，不用通过
PLSQL来实现，直接用connect by即可，当着这里已经有点跑题了，权当兴趣吧:

select substr('JEFFREY',rownum, 1) Letter from dual
con
LETT
----
J
E
F
F
R
E

6 rows selected.

介绍这个例子也是为了引申出两个简单常用的技巧:
1) 构建大数据量的测试数据:
select rownum a from dual connect by rownum<=10000; --- 10000条顺序的纪录就轻松产生了。 或者: select level a from dual connect by level<=10000; 2) 结合case ... when优化query, 通过这种方法在列转行的时候，减少全表扫描的次数: 比如，我要把ID1,ID2,ID3合并起来，他们对应的CHARGE不变: TYPE ID1 ID2 ID3 CHARGE ---------- ------------------------------ ------------------------------ ------------------------------ ---------- 1 0000001 0000002 0000003 100 1 0000006 0 0000008 900 1 0000009 0 0 400 合并结果如下: TYPE ID CHARGE ---------- ------------------------------ ---------- 1 0000001 100 1 0000006 900 1 0000009 400 1 0000002 100 1 0000003 100 1 0000008 900 ++++++++++++ test table ++++++++++++++++++ drop table test; create table test ( type number, id1 varchar2(10) default 0 not null, id2 varchar2(10) default 0 not null, id3 varchar2(10) default 0 not null, charge number ); insert into test values (1,'0000001','0000002','0000003',100); insert into test values (1,'0000006',0,'0000008',900); insert into test values (1,'0000009',0,0,400); commit; +++++++++++++++++++++++++++++++++++++++++++ 常规方法可能是: select type, id1 id, charge from test where id1<>0
union all
select type, id2 id, charge from test where id2<>0
union all
select type, id3 id, charge from test where id3<>0
;

但如果test纪录size很大，三次FTS肯定cost很高，所以我们必须考虑通过case .. when的方法减少FTS的次数:
select type,
case a when 1 then id1
when 2 then id2
when 3 then id3
end
id,charge
from test, (select level a from dual connect by level<=3) lvl where case a when 1 then id1 when 2 then id2 when 3 then id3 end <>0;
从下面的执行计划看，只有一次FTS，而不是之前的三次。
-------------------- Execution Plan ----------------------------------------------
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 60 | 5 (0)|
| 1 | NESTED LOOPS | | 1 | 60 | 5 (0)|
| 2 | VIEW | | 1 | 13 | 2 (0)|
| 3 | CONNECT BY WITHOUT FILTERING| | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)|
|* 5 | TABLE ACCESS FULL | TEST | 1 | 47 | 3 (0)|
----------------------------------------------------------------------------
-------------------------------------------------------------------------------------