Oracle and PostgreSQL recursive query parent-child relationship record syntax differences

Oracle

First, the data

The DB data fields are as follows:

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                          ******                      ***                         ***                                   ***

Second, format
        Select * from .... Where [result filter condition statement]
start with  [and start conditional filter statement]
Connect by prior [and intermediate record filter statement]
Three, find all subordinates
    &N Bsp   SELECT * FROM tablename start with id=1 connect by prior id=pid
Note: This SQL can find all the subordinates of the id=1 data, note that when writing the SQL statement, it is necessary to look at the subordinate from the ID start , so the conditions of the connect by prior clause are         Id=pid
Four, find all superiors
       select * from Tabl ename start with id=5 connect by prior Pid=id
because you are looking for a superior from the ID, the condition of the connect by prior clause is Pid=d

  Select from with task_id = ' 000001 '  by= parent_task_id;

V. Display of results

The results show:

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

PostgreSQL

Querying all child nodes under the parent node

 withRecursive FileInfo (pk_fi_id, F_fi_parentid) as(SELECTpk_fi_id, F_fi_parentid fromT_fileinfoWHEREpk_fi_id= '92719f78-22d6-4db1-a484-dff34de76890'UNION  AllSELECTmm.pk_fi_id, Mm.f_fi_parentid fromT_fileinfo asmmINNER JOINFileInfo asChild onMm.f_fi_parentid=child.pk_fi_id)SELECT  * fromFileInfo

Oracle and PostgreSQL recursive query parent-child relationship record syntax differences