oracle遞迴查詢

標籤：

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

查詢結果：

 

oracle遞迴查詢