oracle和postgresql 遞迴查詢父子關聯性記錄文法區別

標籤：_id   acl   code   pid   條件   節點   查詢   ble   區別   

oracle:

一、資料

db資料欄位如下：

task_id             task_name         t.parent_task_id       ******                     ***                          ***                               ***000001            t1                         ***                                 ***000002            t11                       000001                        ***000005            t12                       000001                         ***000003            t111                    000002                         ***000004            t1111                  000003                         ***000006            t121                    000005                         ***000007            t1211                  000006                         ******                     ***                       ***                                 ***

二、格式
        Select * from …. Where [結果過濾條件陳述式]
　　Start with  [and起始條件過濾語句]
　　Connect by prior [and中間記錄過濾條件陳述式]
三、尋找所有下級
        select * from tablename start with id=1 connect by prior id=pid
　　注意：此sql能尋找id=1的資料的所有下級，寫sql語句時要注意，因為是從id開始尋找下級，所以connect by prior 子句的條件是         id=pid
四、尋找所有上級
       select * from tablename start with id=5 connect by prior pid=id
　　因為是從id開始尋找上級，所以connect by prior 子句的條件是pid=d

select t.task_id ,t.task_name ,t.parent_task_id from t_task t start with task_id=‘000001‘connect by prior task_id = parent_task_id;

五、顯示結果

結果顯示：

task_id                 task_name          t.parent_task_id000001                t1          000002                t11                       000001000003                t111                     000002000004                t1111                    000003000005                t12                       000001000006                t121                     000005000007                t1211                   000006

postgresql:

查詢父節點下所有的子節點

WITH recursive fileinfo (pk_fi_id,  f_fi_parentid)AS(SELECT        pk_fi_id ,        f_fi_parentid     FROM        t_fileinfo    WHERE        pk_fi_id = ‘92719f78-22d6-4db1-a484-dff34de76890‘UNION ALLSELECT            mm.pk_fi_id ,            mm.f_fi_parentid         FROM            t_fileinfo AS mmINNER JOIN fileinfo AS child ON mm.f_fi_parentid = child.pk_fi_id)SELECT  *FROM fileinfo

 

oracle和postgresql 遞迴查詢父子關聯性記錄文法區別