PROGRAMer NEVER SLEEPS

前言

• 樹狀結構的遞迴查詢法，含反轉結果的處理方式

 

基本介紹

開發環境

1. Oracle

 

參考資料

• https://blog.darkthread.net/blog/oracle-connect-by/
• https://stackoverflow.com/questions/1381256/reverse-in-oracle-this-path-z-y-x-to-x-y-z#comment1221949_1381495

---

實作 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

/台北市/南港區/研究院路一段

 

 Written By Felix Hsieh