MySql和Oracle的递归查询

女爷i 2022-05-18 22:48 227阅读 0赞

当数据库中的表格属于自关联表的时候,当根据parent\_id去查询他所有的子集的时候(并且不知道树结构都多少层),就需要递归查询了.  
        我们经常遇到的情况有省市县三级联动,总银行和分支银行.我们在创建表的时候:  
        第一方面在主键ID上上做区分:例如江苏省id为10,南京市的id为10110,可以给前端页面一个很好的分类依据;  
        第二方面我们会加一个字段parent\_id和他的上级有一个关联,给后台查询提供依据;  
        正常情况下是用不到递归查询的,但是也有例外,比如图书馆字段表,分层可能不止三级,不同类图书分的层次数也不确定,当想查询某一类图书(“人文”)下的所有的图书的时候,就需要递归了.  
我只接触过Oracle和MySql上的递归查询,这里做一个应用介绍;  
  
**1. 使用Oracle实现递归查询**

oracle比mysql要简单的多,可以使用其内置函数
    start with 条件1(一般为parent_id="xxx").....connect by prior 条件2 (一般为id = parent_id注意顺序不能乱,不可写成parent_id=id)

案例  
![这里写图片描述][70]  
sql语句

SELECT * FROM "RC"."T_USER_TYPE" T START WITH T .PARENT_ID = 11 CONNECT BY PRIOR T .GUID = T .PARENT_ID;

结果  
![这里写图片描述][70 1]  
  
**2. 使用MySql实现递归查询**

mysql相对于Oracle就复杂一点,因为mysql没有内置start with 类似的函数,这时候我们就自定义个 一个函数,里面依赖了mysql的CAST();CONCAT();GROUP_CONCAT();FIND_IN_SET()函数.

这里对上述函数先做一个简单介绍,方便理解自定义函数:  
1. cast()

Cast(字段名 as 转换的类型 )，其中类型可以为：
    
    CHAR[(N)] 字符型 
    DATE 日期型
    DATETIME 日期和时间型
    DECIMAL float型
    SIGNED int
    TIME 时间型

案例—>这些都是大家常用到的情形

select cast(date as signed) as date from table1;
    结果:date：20151103153126
    
    select cast(date as char) as date from table1;
    结果:date：2015-11-03 15:31:26
    
    select cast(date as datetime) as date from table1;
    结果:date：2015-11-03 15:31:26
    
    select cast(date as date) as date from table1;
    结果:date：2015-11-03
    
    select cast(date as time) as date from table1;
    结果:date：15:31:26
    
    select cast(num as decimal(10, 2)) as num from table2 结果:num：20.00

2 . concat()

此函数和前端js的数组Array的concat()类似,链接字符串;
     concat('11','22','33')
     结果:112233

3 . group\_concat()

通常与groupby一起用,将查询出来的字段拼接,默认以逗号分隔
    
    普通查询
    mysql> select * from aa; 
    +------+------+ 
    | id| name | 
    +------+------+ 
    |1 | 11| 
    |1 | 20| 
    |1 | 23| 
    |2 | 20| 
    |3 | 23 | 
    |3 | 300 | 
    
    使用group_concat查询
    mysql> select id,group_concat(name) from aa group by id; 
    +------+--------------------+ 
    | id| group_concat(name) | 
    +------+--------------------+ 
    |1 | 11,20,23| 
    |2 | 20 | 
    |3 | 23,300|

4 . find\_in\_set()函数

类似于StringUtils的containt方法,因为两个参数本质都是字符串;也类似于Conllection的
    contain方法,因为strlist一般是以逗号分隔的字符串很像数组,而str更像其中的一个元素;
    
    FIND_IN_SET(str,strlist)
    
    看个例子就明白了
    select FIND_IN_SET("a","a,b,c")
    结果: 1
    
    select FIND_IN_SET("a","a,b,c,a")
    结果: 1
    
    select FIND_IN_SET("z","a,b,c,a")
    结果: 0

mysql内置函数介绍完了,现在要递归查询

这一段是在一个大神博客上改写的(请谅解)
    自定义一个函数 queryChildrenAreaInfo
    
    -- 如果已经存在此函数就删除
    DROP FUNCTION IF EXISTS queryChildrenAreaInfo;
    -- 创建函数
    CREATE FUNCTION queryChildrenAreaInfo(areaId INT) -- 定义返回类型 RETURNS VARCHAR(4000) -- 方法体 BEGIN -- 定义变量 DECLARE sTemp VARCHAR(4000);
    DECLARE sTempChd VARCHAR(4000);
    
    SET sTemp='$';
    SET sTempChd = CAST(areaId AS CHAR);
    -- 递归循环
    WHILE sTempChd IS NOT NULL DO SET sTemp= CONCAT(sTemp,',',sTempChd);
    --- 查询所有的id并拼接字符串,条件是parent_id在此字符串中,如果没有了则sTempChd为null
    SELECT GROUP_CONCAT(id) INTO sTempChd FROM f_user_type WHERE FIND_IN_SET(parent_Id,sTempChd)>0;
    END WHILE;
    RETURN sTemp;
    END;
    
    
    当调用此方法的时候,返回的是给定id对应的分类下的所有分类的的id的用逗号分隔的字符串
    
    select queryChildrenAreaInfo(11) 结果:$,11,11001,11002,11003,11004,11005,11006,11007,11009,11010,11011,11012,11013,11014,11021,11022,11023,11024,11025,11026,11027 我们查询只需查id在此字符串中即可(不可以用in,因为返回的是一个字符串而不是"1101","1102") SELECT * FROM f_user_type WHERE FIND_IN_SET(id,queryChildrenAreaInfo(11));

![这里写图片描述][70 2]

[70]: /images/20220519/f2251e523a0a444b944575d744556bbb.png
[70 1]: /images/20220519/536c0bad3e4840788ecdf589f936bab1.png
[70 2]: /images/20220519/8c5b762f8c474a98a66c1bc7730d8ee2.png