你的位置:首页 > 数据库

[数据库]oracle和postgresql 递归查询父子关系记录语法区别


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_id
000001                t1          
000002                t11                       000001
000003                t111                     000002
000004                t1111                    000003
000005                t12                       000001
000006                t121                     000005
000007                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