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

一、语法结构

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;

        

(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;

        

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

        

                注意：可以嵌套使用

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

                );

                

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

        

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

        

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

        

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

        

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

        

拓展：

(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;