oracle和postgresql 遞歸查詢父子關係記錄語法區別

oracle:

一、數據

db數據欄位如下：

二、格式
        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;

五、顯示結果

結果顯示：

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 ALL
SELECT
            mm.pk_fi_id ,
            mm.f_fi_parentid 
        FROM
            t_fileinfo AS mm
INNER JOIN fileinfo AS child ON mm.f_fi_parentid = child.pk_fi_id
)
SELECT
  *
FROM fileinfo