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

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

## 實作 Part.1 (反序搜尋)

          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 (反轉結果)

          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 (收納)

          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|
| - |
|/台北市/南港區/研究院路一段|