oracle 和 mysql 中的递归查询用法

oracle

使用场景

用于B树等结构的递归查询，可以通过树中的一个节点来遍历该树

语法

start with connect by prior

语法：

    select … from tablename
    start with 条件1
    connect by *** prior ***
    where 条件3;

创建table及值

create table TEST_TABLE
(
  parentid VARCHAR2(10),
  subid    VARCHAR2(10)
);

select * from  TEST_TABLE

insert into test_table (PARENTID, SUBID) values ('1', '2');
insert into test_table (PARENTID, SUBID) values ('1', '3');
insert into test_table (PARENTID, SUBID) values ('2', '4');
insert into test_table (PARENTID, SUBID) values ('2', '5');
insert into test_table (PARENTID, SUBID) values ('3', '6');
insert into test_table (PARENTID, SUBID) values ('3', '7');
insert into test_table (PARENTID, SUBID) values ('5', '8');
insert into test_table (PARENTID, SUBID) values ('5', '9');
insert into test_table (PARENTID, SUBID) values ('7', '10');
insert into test_table (PARENTID, SUBID) values ('7', '11');
insert into test_table (PARENTID, SUBID) values ('10', '12');
insert into test_table (PARENTID, SUBID) values ('10', '13');

树的结构如下：

语法说明

1、start with ：

遍历开始的节点，可以是父节点，也可以是子节点

父节点：

遍历的结果就是以3为父节点向下查询的所有数据，注意看PARENTID列，parentID是从3开始查询的

SELECT  T.PARENTID, T.SUBID, LEVEL FROM TEST_TABLE T
START WITH T.parentid = '3' CONNECT BY PARENTID = PRIOR SUBID
ORDER BY LEVEL 

 

子节点：

遍历的结果就是以7位子节点，向下查询的所有数据，注意看SUBID列，subid 是从7开始查询的

SELECT T.PARENTID, T.SUBID, LEVEL FROM TEST_TABLE T
START WITH T.SUBID = '7' CONNECT BY PARENTID = PRIOR SUBID
ORDER BY LEVEL 

 

 2、CONNECT BY  PRIOR 连接条件

关键词就是prior，prior和谁放在一起，就往谁的方向遍历，至于prior放在等号的左侧还是右侧，没有限制

往子方向进行遍历

SELECT T.PARENTID, T.SUBID, LEVEL FROM TEST_TABLE T
START WITH T.SUBID = '7' CONNECT BY PARENTID = PRIOR SUBID
ORDER BY LEVEL 

      

 

往父方向进行遍历

SELECT T.PARENTID, T.SUBID, LEVEL FROM TEST_TABLE T
START WITH T.SUBID = '7' CONNECT BY PRIOR PARENTID = SUBID
ORDER BY LEVEL 

          

 3、level关键字

在具有树结构的表中，每一行数据都是树结构中的一个节点，由于节点所处的层次位置不同，所以每行记录都可以有一个层号。层号根据节点与根节点的距离确定。不论从哪个节点开始，该起始根节点的层号始终为1，根节点的子节点为2。需要注意的是这里所说的根节点指的是start with后面的那个节点；

mysql

find_in_set 函数

函数语法：

find_in_set(str,strlist) :str 代表要查询的字符串 ， strlist 是一个以逗号分隔的字符串，如 ('a,b,c')。

此函数用于查找 str 字符串在字符串 strlist 中的位置，返回结果为 1 ~ n 。若没有找到，则返回0

select FIND_IN_SET('b','a,b,c,d');

 

在对表数据进行查询时，它还有一种用法，如下 

select * from dept where FIND_IN_SET(id,'1000,1001,1002');

 因此，在MySQL中 ，要解决递归查询（向子节点递归），首先找到一个包含当前节点和所有子节点的并且以逗号拼接的字符串strlist，传入find_in_set()函数，就可以查出递归数据了。

MySQL 自定义函数，实现递归查询

向下查询函数：

delimiter $$ 
drop function if exists get_child_list$$ 
create function get_child_list(in_id varchar(10)) returns varchar(1000) 
begin 
    declare ids varchar(1000) default ''; 
    declare tempids varchar(1000); 

    set tempids = in_id; 
    while tempids is not null do 
        set ids = CONCAT_WS(',',ids,tempids); 
        select GROUP_CONCAT(id) into tempids from dept where FIND_IN_SET(pid,tempids)>0;  
    end while; 
    return ids; 
end  
$$ 
delimiter ; 

语法解释：

（1） delimiter ，意思是遇到 $$ 才结束，并执行当前语句。

（2）drop function if exists get_child_list 来结束并执行语句。 因为，这里需要和下边的函数体单独区分开来。

（3）create function get_child_list 创建函数。并且参数传入一个根节点的子节点id，需要注意一定要注明参数的类型和长度，如这里是 varchar(10)。returns varchar(1000) 用来定义返回值参数类型。

（4）begin 和 end 中间包围的就是函数体。用来写具体的逻辑。

（5）declare 用来声明变量，并且可以用 default 设置默认值。

这里定义的 ids 即作为整个函数的返回值，是用来拼接成最终我们需要的以逗号分隔的递归串的。

而 tempids 是为了记录下边 while 循环中临时生成的所有子节点以逗号拼接成的字符串。

（6） set 用来给变量赋值。此处把传进来的根节点赋值给 tempids 。

（7） while do ... end while; 循环语句，循环逻辑包含在内。注意，end while 末尾需要加上分号。

循环体内，先用 CONCAT_WS 函数把最终结果 ids 和 临时生成的 tempids 用逗号拼接起来。

然后以 FIND_IN_SET(pid,tempids)>0 为条件，遍历在 tempids 中的所有 pid ，寻找以此为父节点的所有子节点 id ，并且通过 GROUP_CONCAT(id) into tempids 把这些子节点 id 都用逗号拼接起来，并覆盖更新 tempids 。

等下次循环进来时，就会再次拼接 ids ，并再次查找所有子节点的所有子节点。循环往复，一层一层的向下递归遍历子节点。直到判断 tempids 为空，说明所有子节点都已经遍历完了，就结束整个循环。

第一次循环：
  tempids=1000    ids=1000    tempids=1001,1002 (1000的所有子节点)
第二次循环：
  tempids=1001,1002     ids=1000,1001,1002     tempids=1003,1004,1005,1013 (1001和1002的所有子节点)
第三次循环：
  tempids=1003,1004,1005,1013 
  ids=1000,1001,1002,1003,1004,1005,1013 
  tempids=1003和1004和1005及1013的所有子节点
...
最后一次循环，因找不到子节点，tempids=null，就结束循环。

 

（8）return ids; 用于把 ids 作为函数返回值返回。

（9）函数体结束以后，记得用结束符 $$ 来结束整个逻辑，并执行。

（10）最后别忘了，把结束符重新设置为默认的结束符分号 。

自定义函数做好之后，我们就可以用它来递归查询我们需要的数据了。如，我查询北京研发部的所有子节点。
 

向上递归查询

delimiter $$ 
drop function if exists get_parent_list$$ 
create function get_parent_list(in_id varchar(10)) returns varchar(1000) 
begin 
    declare ids varchar(1000); 
    declare tempid varchar(10); 

    set tempid = in_id; 
    while tempid is not null do 
        set ids = CONCAT_WS(',',ids,tempid); 
        select pid into tempid from dept where id=tempid; 
    end while; 
    return ids; 
end 
$$ 
delimiter ; 
 

参考文档

同事问我MySQL怎么递归查询，我懵逼了_烟雨星空的博客-CSDN博客