Summary_Oracle 根据父节点查询子节点(递归查询)

时间:2024-03-21 19:00:37

一、语法结构

SELECT ... FROM    + 表名  START WITH + 条件1 CONNECT BY PRIOR + 条件2  WHERE  + 条件3

条件1:是根节点的限定语句,当然可以放宽限定条件,以取得多个根节点,也就是多棵树;在连接关系中,除了可以使用列明外,还允许使用列表达式。START WITH 子句为可选项,用来标识哪个节点作为查找树形结构的根节点。若该子句省略,则表示所有满足查询条件的行作为根节点。

条件2:是连接条件,其中用PRIOR表示上一条记录,例如CONNECT BY PRIOR STUDENT_ID = GRADE_ID,意思就是上一条记录的STUDENT_ID是本条记录的GRADE_ID,即本记录的父亲是上一条记录。CONNECT BY子句说明每行数据将是按照层次顺序检索,并规定将表中的数据连入树形结构的关系中。PRIOR运算符必须放置在连接关系的2列中某一个的前面。对于节点间的父子关系,PRIOR运算符在一侧表示父节点,在另一侧表示子节点,从而确定查找树结构的顺序是自顶向下,还是自底向上。

条件3:是过滤条件,用于对返回的记录进行过滤。

注意:

1,CONNECT BY PRIOR是结构化查询中用到的;

2,START WITH... CONNECT BY PRIOR...的作用,简单来说,就是将一个树状结构存储在一张表里。

二、应用场景

START WITH... CONNECT BY PRIOR...常见的用法,是用来遍历含有父子关系的表结构中。比如省市关系,一个省下面包含多个城市,如果城市基本信息表中,包含有属于哪个省级的字段,那么如果要遍历所有的城市,我们就可以使用START WITH... CONNECT BY PRIOR...。

三、MySQL实现Start With...Connect By Prior 递归查询

(1)、创建表

        create table treeList(
               id varchar(10), -- 节点ID
               name varchar(10), -- 节点名称
               pId varchar(10) -- 父ID

        );

(2)、插入数据

        insert into treeList values(1,'中国',null);
        insert into treeList values(2,'北京',1);
        insert into treeList values(3,'上海',1);
        insert into treeList values(4,'深圳',1);
        insert into treeList values(5,'海淀',2);
        insert into treeList values(6,'朝阳',2);
        insert into treeList values(7,'昌平',2);

        insert into treeList values(8,'丰台',2);

        Oracle中根据父节点查询子节点:

        SELECT * FROM treeList START WITH ID IN (1) CONNECT BY PRIOR ID=PID;

        Summary_Oracle 根据父节点查询子节点(递归查询)

(3)、创建函数 getChildList 查询子节点(包含父节点)

        CREATE FUNCTION getChildList (rootId VARCHAR(100)) -- rootId为要查询的节点
        RETURNS VARCHAR(1000)
        BEGIN
                DECLARE pTemp VARCHAR(1000); -- 定义两个临时变量 
                DECLARE cTemp VARCHAR(1000); -- 定义两个临时变量

                SET pTemp = '';  
                SET cTemp = rootId;

                WHILE cTemp is not null DO  
                       if (pTemp = '') then
                                 SET pTemp = cTemp;
                       elseif(pTemp <> '') then
                                 SET pTemp = concat(pTemp,',',cTemp); -- 所有节点连接成字符串
                       end if;
                       SELECT group_concat(
id) INTO cTemp FROM treeList  WHERE FIND_IN_SET(pId,cTemp)>0; 
                 END WHILE;  
                 RETURN pTemp;  

        END;

(4)、执行方法

使用我们直接利用Find_In_Set函数配合这个getChildList函数

        (a)、SELECT getChildList(1) FROM dual;

        Summary_Oracle 根据父节点查询子节点(递归查询)

        (b)、SELECT * FROM treeList WHERE FIND_IN_SET(id, getChildList(1));

        Summary_Oracle 根据父节点查询子节点(递归查询)

                注意:可以嵌套使用

                SELECT * FROM treeList WHERE id in(
                SELECT id FROM treeList WHERE FIND_IN_SET(
id, getChildList(1))

                );

                Summary_Oracle 根据父节点查询子节点(递归查询)

        (c)、SELECT * FROM treeList WHERE FIND_IN_SET(id, getChildList(2));

        Summary_Oracle 根据父节点查询子节点(递归查询)

        (d)、SELECT * FROM treeList WHERE FIND_IN_SET(id, getChildList(3));

        Summary_Oracle 根据父节点查询子节点(递归查询)

        (e)、SELECT * FROM treeList WHERE FIND_IN_SET(pid, getChildList(1));

        Summary_Oracle 根据父节点查询子节点(递归查询)

        (f)、SELECT * FROM treeList WHERE FIND_IN_SET(pid, getChildList(2));

        Summary_Oracle 根据父节点查询子节点(递归查询)

        (g)、SELECT * FROM treeList WHERE FIND_IN_SET(pid, getChildList(3));

        Summary_Oracle 根据父节点查询子节点(递归查询)

拓展:

(5)、创建函数 getParentList 查询父节点树

        CREATE FUNCTION getParentList(rootId VARCHAR(10000)) 
        RETURNS VARCHAR(10000) CHARSET utf8  
        BEGIN   
                DECLARE sParentList VARCHAR(10000);   
                DECLARE sParentTemp VARCHAR(10000);   
          
                SET sParentTemp =rootId;   
          
                WHILE sParentTemp IS NOT NULL DO   
                        IF (sParentList IS NOT NULL) THEN   
                                SET sParentList = CONCAT(sParentTemp,',',sParentList);   
                        ELSE   
                                SET sParentList = CONCAT(sParentTemp);   
                        END IF;   
                        SELECT GROUP_CONCAT(
pid) INTO sParentTemp FROM treeList WHERE FIND_IN_SET(id,sParentTemp)>0;   
                END WHILE;   
                RETURN sParentList;   

         END;

(6)、创建函数 getBetweenNodes 查询父、子节点中的节点数

        CREATE FUNCTION getBetweenNodes(startNodeId VARCHAR(100), endNodeId VARCHAR(100)) 
      RETURNS VARCHAR(10000) CHARSET utf8  
      BEGIN  
           DECLARE sBetweenNodes VARCHAR(10000);  
           SELECT GROUP_CONCAT(id) INTO sBetweenNodes FROM treeList 
       WHERE FIND_IN_SET(id, getParentNodes(endNodeId)) 
       AND FIND_IN_SET(pid, getChildNodes(startNodeId));  
           RETURN sBetweenNodes;  
      END;