????Oracle?е?select????????START WITH...CONNECT BY PRIOR????????????connect by ??????????????????????????
????select * from tablename start with cond1
????connect by cond2
????where cond3;
?????????????????????洢??????????????????д??????????:
????id??parentid??????????????????parent??????????γ???????????
?????????????????????????????????м????
????????COND1??????????????????????????????????????????????????????
????COND2??????????????????PRIOR????????????????? CONNECT BY PRIOR ID=PRAENTID?????????????ID??????????PRAENTID??????????????????????????
????COND3???????????????????????м?????й????
????????oracle???м??????(?????)
????DEPTID NUMBER ????id
????PAREDEPTID NUMBER ??????id(???????id)
????NAME CHAR (40 Byte) ????????
???????????????????.
????select * from persons.dept start with deptid=76 connect by prior paredeptid=deptid
???????????????????.
????select * from persons.dept start with paredeptid=0 connect by prior deptid=paredeptid
?????????level ?????????????.
????select a.*??level from persons.dept a start with paredeptid=0 connect by prior deptid=paredeptid
????PS??start with ??????????????????????????????????
????connect by prior??????????????н??????
??????prior???????????????????????????У????????е??????
????????
????select * from table
????start with org_id = 'HBHqfWGWPy'
????connect by prior org_id = parent_id;
?????????????????????洢??????????????????д??????????:
????org_id??parent_id??????????????????parent??????????γ???????????
?????????????????????????????????м????
???????У?
????????1 ??????????????????????????????????????????????????????
????????2 ??????????????????PRIOR?????????????
???????? CONNECT BY PRIOR org_id = parent_id?????????????org_id ??????????parent_id??????????????????????????
????????3 ???????????????????????м?????й????
?????????????￡?
??????????????????????????????????????????????????????Σ?????????????￡?
????????????????????
???????????????y??
?????????????ж?y??????δ??????????????У????????????δ???????????????е????????????е??????
?????????????y???????????????????????е??崠??
???????崠????????y???????????е??????衣
?????????????????????????????????????????????
????1?? ??????????
????????????????????У??????????ι??????????????????е??????м???????????? ?? EMP ???е? EMPNO ?? MGR ?? EMPNO ????ù??????? MGR ??????ù????????????????? MGR ?????????? EMPNO ????????????ж??????????????? MGR ????????????????????????????????????????????
?????? SELECT ????????? CONNECT BY ?? START WITH ???????????е??????????????????????￡?
????SELECT ??????
????CONNECT BY {PRIOR ???? 1= ???? 2| ???? 1=PRIOR ???? 2}
????[START WITH] ??
???????У? CONNECT BY ???????????????????????????????漲?????е???????????????????С? PRIORY ????????????????????????????????????檔??????????????? PRIOR ???????????????????????????????????????????????????????????????????????????????У???????????????????????????б?? ??? START WITH ????????????????????????????????????????????????????????????????????????????????
????START WITH?? ????????????????????????????????????
????2?? ???? PRIOR
????????? PRIOR ??????????????λ????????????????????
????PRIOR ?????? CONNECT BY ????е??????????????????????????????????????????????????????????????????????? ???????磺
????CONNECT BY PRIOR EMPNO=MGR
????PIROR ??????????? CONNECT BY ????е??????????????????????????????????????????????????????????????????????? ?????????磺
????CONNECT BY EMPNO=PRIOR MGR
?????????????????????????????
????3?? ?????????????
????????????2???????????????????????????????????κν?????????????????2???????????????????y?????????????????
????4????? LEVEL
?????????????????У?????????????????е?????????????????????λ?ò??????????м??????????????????????????????????????????????????????????????????????? 1 ????????????? 2 ?? ?????????
????5??????????ü?
??????????????в??????????????е??Щ?У???????????е???????????? WHERE ??????????????е???????????????е???????????????????????????????????????????????????????????
????6?????????
????????????????????????????????????????? ORDER BY ????????????????????????????????????????