connect by的一些想法

沉浸了良久，对connect by,sys_connect_by_path等始终不得要领，没有感性认识，实际操作中不能灵活运用，心中苦恼。我现在的状态也许就像人家说的正处在一个坎上，也许哪天不经意间，或者脑袋被门挤了便能豁然开朗。谨将这些时日收集的各种相关例子贴出，期盼俺的尽快开窍，同时也迫切期待有入了门的前辈能来指点~~~

SYS_CONNECT_BY_PATH
格式：SYS_CONNECT_BY_PATH(column,char)
描述：SYS_CONNECT_BY_PATH只在分层查询中有效。它返回从根到节点的列值的路径，以及按CONNECTY　BY条件返回的每个行的由

char分隔的列值。
示例：
With tmp5 As(
Select '001' emp_id,'101' manager_id From dual
Union All
Select '001','102' From dual
Union All
Select '101','201' From dual
Union All
Select '102','202' From dual
Union All
Select '002','102' From dual
Union All
Select '003','103' From dual
Union All
Select '103','203' From dual
Union All
Select '201','301' From dual
Union All
Select '203','303' From dual
)

select tmp5.*,sys_connect_by_path(emp_id,' ') from tmp5 Start With emp_id='001' Connect By emp_id=Prior manager_id

CONNECT BY
格式：select expression from table
where condition
connect by [prior] expression= [prior] expression
start with expression = expression
order by expression
描述：CONNECT BY是一个运算符，在select 语句中用来建交反映树形结构数据，START　WITH指示从树的何处开始。有下列几条规

则.
1>与CONNECT　BY表达式有关的PRIOR的位置确定哪个表达式标识根，哪个表达式标识树的分支。
2>WHERE子句将从树中删除个别的节点，但不会删除其子孙
3>CONNECT　BY中的限定条件将删除个别节点及其所有子孙。
4>CONNECT　BY不能与where子句中的表连接一起使用。
示例：
1、取规则序列
Select Sysdate+Level From dual Connect By Level<=10

2、With tt As (
Select 'y1' Id,'电子电器' Name,'root' fid From dual
Union All
Select 'y2','家俱','root' From dual
Union All
Select 'e1','电视机','y1' From dual
Union All
Select 'e2','冰箱','y1' From dual
Union All
Select 's1','纯平','e1' From dual
Union All
Select 's2','液晶','e1' From dual
Union All
Select 'a1','高端','s1' From dual
Union All
Select 'a2','中端','s1' From dual
Union All
Select 'a3','低端','s1' From dual
)
Select * From tt Start With Id='e1' Connect By fId=Prior id