MySql和Oracle的递归查询

        当数据库中的表格属于自关联表的时候,当根据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)

案例

sql语句

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

结果


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