1、从上往下查询
使用 start with a= ‘起始ID‘ connect by prior a= b
a为子项，b为父项。
select F_CA_CODE,F_CA_PARENTCODE,LEVEL from RGT_UNIT start with F_CA_CODE = ‘0‘ connect by prior F_CA_CODE = F_CA_PARENTCODE
查询结果：
其中LEVEL为oracle内置函数
如果加入查询条件需要在start with前面加入：
select F_CA_CODE,F_CA_PARENTCODE,LEVEL from RGT_UNIT where LEVEL=3 start with F_CA_CODE = ‘0‘ connect by prior F_CA_CODE = F_CA_PARENTCODE
查询结果：
2、从下往上查询
使用 start with a= ‘起始ID‘ connect by prior b= a
a为子项，b为父项。
select F_CA_CODE,LEVEL from RGT_UNIT start with F_CA_CODE=‘10011‘ connect by prior F_CA_PARENTCODE =F_CA_CODE
查询结果：
其中LEVEL为oracle内置函数
如果加入查询条件需要在start with前面加入：
select F_CA_CODE,LEVEL from RGT_UNIT where LEVEL=2 start with F_CA_CODE=‘10011‘ connect by prior F_CA_PARENTCODE =F_CA_CODE
查询结果：
原文：http://www.cnblogs.com/no27/p/4221650.html