ORACLE 数据库的级联查询 一句sql搞定（部门多级）
在ORACLE 数据库中有一种方法可以实现级联查询?select ?*??????????????? //要查询的字段from table????????????? //具有子接点ID与父接点ID的表?start with selfid=id????? //给定一个startid(字段名为子接点ID，及开始的ID号)connect by prior selfid=parentid?????? //联接条件为子接点等于父接点?这个SQL主要用于级联查询，给一个父接点可以查出所有的子接点。及子接点的子接点，一查到底，很实用。?例：航班表airline，如何用sql语句查询出从广州出发能到达的所有目的地，允许任意中转。?FLIGHTNO?ORIGIN??DESTINATION
-------------------------------------------
cz3001??CAN??CSX
cz3002??CAN??SHA
cz3003??CSX??SHA
cz3004??CSX??PEK
cz3005??SHA??XIY
cz3006??SHA??SWA
cz3007??PEK??URC
cz3008??PVC??AMS
cz3009??WUH??PVC
cz3010??WUH??XIY?这里根就是CAN，SQL语句如下：?select?t.destination?from?airline?t?start with?origin='CAN'?connect by prior?destination?=?origin;?查询结果：
DESTINATION
-------------------
CSX??
SHA??
XIY??
SWA??
PEK??
URC??
SHA??
XIY??
SWA??
9 rows selected.?－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－??在网上看到下面的例子应该更容易理解些，转载一下：??数据结构如下：
t1
?t11
???? t111
??????? t1111
?t12
???? t121
??????? t1211
?
db数据字段如下：
task_id???????????? task_name???????? t.parent_task_id?????? ***
***???????????????????? ***????????????????????????? ***?????????????????????????????? ***
000001??????????? t1???????????????????????? ***???????????????????????????????? ***
000002??????????? t11?????????????????????? 000001??????????????????????? ***
000005??????????? t12?????????????????????? 000001???????????????????????? ***
000003??????????? t111??????????????????? 000002???????????????????????? ***
000004??????????? t1111????????????????? 000003???????????????????????? ***
000006??????????? t121??????????????????? 000005???????????????????????? ***
000007??????????? t1211????????????????? 000006???????????????????????? ***
***???????????????????? ***?????????????????????? ***???????????????????????????????? ***
查询语句：
select t.task_id ,t.task_name ,t.parent_task_id?
from t_task t?
start with task_id='000001'
connect by prior task_id = parent_task_id;
结果显示：
task_id???????????????? task_name????????? t.parent_task_id
000001??????????????? t1???????????
000002??????????????? t11?????????????????????? 000001
000003??????????????? t111???????????????????? 000002
000004??????????????? t1111??????????????????? 000003
000005??????????????? t12?????????????????????? 000001
000006??????????????? t121???????????????????? 000005
000007??????????????? t1211?????????????????? 000006
strat with 指定层次开始的条件，即是说满足这个条件的行即可以做为层次树的最顶层?
??
connect by prior指层之间的关联条件，即什么样的行是上层行的子行（自连接条件）
?
select level ,id,name,parentid from temptable2?
? connect by prior parentid(属于顶层的列)=id(子层的列)? start with id =1
