---
# System prepended metadata

title: Oracle查詢樹狀結構資料(遞迴)
tags: [CONNECT BY, ORACLE]

---

# Oracle查詢樹狀結構資料(遞迴)
###### tags: `ORACLE` `CONNECT BY`

- [name=Felix Hsieh]
- [time=Thu, May 26, 2022 1:16 AM]
---

## 實作 Part.1 (反序搜尋)
```sql=
          SELECT Product_Category_NO
               , LPAD(' ', (LEVEL - 1) * 4) || Product_Category_Name   AS PartName     --自動縮排，每次4個[space]
               , SYS_CONNECT_BY_PATH(Product_Category_Name,'/')        AS PartPath     --自動組裝路徑，以 / 作為分隔符號
               , LEVEL                                                 AS "LEVEL"      --遞迴的層數
            FROM Product_Category_Table
      START WITH Product_Category_NO   = '123456'              --遞迴搜尋的[起點]
CONNECT BY PRIOR Parent_Category_NO = Product_Category_NO;     --以CONNECT BY 定義主從關係，父項欄位前要加上PRIOR
```
### 執行結果
| Product_Category_NO | PartName     | PartPath                    | LEVEL |
| ------------------- | ------------ | --------------------------- | ----- |
| 12                  | 台北市       | /研究院路一段/南港區/台北市 | 3     |
| 1234                | 南港區       | /研究院路一段/南港區        | 2     |
| 123456              | 研究院路一段 | /研究院路一段               | 1     |

>從上表可以看到，由於我們的資料若是是由後往前做查詢，那出來的資料就會是反向的...
>
>這個可能跟我們想要的結果不符
>
>所以…

## 實作 Part.2 (反轉結果)
```sql=+
          SELECT Product_Category_NO
               , LPAD(' ', (LEVEL - 1) * 4) || sub_path AS PartName  --自動縮排，每次4個[space]
               , SYS_CONNECT_BY_PATH(sub_path, '/')     AS PartPath  --自動組裝路徑，以 / 作為分隔符號
               , LEVEL                                  AS "LEVEL"   --遞迴的層數
            FROM (      
                              SELECT Product_Category_NO
                                   , Product_Category_Name   AS sub_path
                                   , LEVEL                   AS "RK"      --遞迴的層數
                                FROM Product_Category_Table
                          START WITH Product_Category_NO   = '123456'              --遞迴搜尋的[起點]
                    CONNECT BY PRIOR Parent_Category_NO = Product_Category_NO      --以CONNECT BY 定義主從關係，父項欄位前要加上PRIOR
                 )
CONNECT BY PRIOR RK - 1 = RK
      START WITH RK = (6); --因為這邊是要逆向搜尋，所以這邊要填入的是最大的階層數
```
### 執行結果
| Product_Category_NO | PartName     | PartPath                    | LEVEL |
| ------------------- | ------------ | --------------------------- | ----- |
| 12                  | 台北市       | /台北市                     | 1     |
| 1234                | 南港區       | /台北市/南港區              | 2     |
| 123456              | 研究院路一段 | /台北市/南港區/研究院路一段 | 3     |

>如此以來...若碰到需要反向搜尋時，這就會是我們需要的結果了..
>
>若只是需要取用最後一筆的PartPath的話…
>
>加個MAX()就行了，如下....

## 實作 Part.3 (收納) 
```sql=+
          SELECT MAX(SYS_CONNECT_BY_PATH(sub_path, '/'))     AS PartPath  --自動組裝路徑，以 / 作為分隔符號
            FROM (      
                              SELECT Product_Category_NO
                                   , Product_Category_Name   AS sub_path
                                   , LEVEL                   AS "RK"      --遞迴的層數
                                FROM Product_Category_Table
                          START WITH Product_Category_NO   = '123456'              --遞迴搜尋的[起點]
                    CONNECT BY PRIOR Parent_Category_NO = Product_Category_NO      --以CONNECT BY 定義主從關係，父項欄位前要加上PRIOR
                 )
CONNECT BY PRIOR RK - 1 = RK
      START WITH RK = (6); --因為這邊是要逆向搜尋，所以這邊要填入的是最大的階層數
```
### 執行結果
|PartPath|
| - |
|/台北市/南港區/研究院路一段|