由于 mysql 本身并没有递归方法,并不像oracle那么方便,所以出现这个需求的时候,很是头疼了一久。
网上查到的有两种办法:
一、 使用 @变量 进行迭代
二、 使用 存储过程,进行遍历。
//*************************************************************************************************************************************
先说第一种办法吧,第一种办法,乍一看很酷啊,一般像我这种初次接触 @变量的人,一下子可能都看不懂。
但是怎么说呢? 看似很美好。先看一下本体吧:
初始条件:sc_pubcompany 保存有每个机构的详细信息及上级机构代码
目标: 获取当前机构及所有上级机构
SELECT DISTINCT
@pv AS _deptCode,
(
SELECT
@pv := upperDeptCode
FROM
sc_pubcompany
WHERE
deptCode = _deptCode
)
FROM
sc_pubcompany b,
(SELECT @pv := '2000000115') initialisation
对于这段代码,我是这样理解的:
1、 from 后的 @pv 作为初始化参数,因为要知道 @变量,在每次会话时,不会被自动重置,也就是说,如果你在一个session中多次使用 @变量,那么后几次的执行起点,是上一次的执行结果。
2、 from后的 @pv 除了初始化的作用外,另一个作用是在你原始数据表后,附加一列,初始机构
3、 迭代部分发生在查询部分,需要注意的是,向上迭代, 迭代要写在 select 后, 如果是向下迭代的话,迭代就要放在 where 之后了。 这个为什么,我也没想明白。
4、 大家可以用 explain 看一下,这个SQL虽然外表很酷,但是实际上,执行它需要巨大的开销,主要初始化的时候,@pv这个,会导致生成一张临时表,之后的所有查询都是基于临时表的。
5、 1000条数据,查询所有机构的上级,总共耗时 78 s。
6、 供有兴趣的同学看一下,递归下级的查询:
SELECT
deptCode,
deptCName,
upperDeptCode
FROM
temp,
(SELECT
@pv := '2000000000') initialisation
WHERE (
FIND_IN_SET(upperDeptCode, @pv) > 0
AND @pv := CONCAT(@pv, ',', deptCode)
)
//*************************************************************************************************************************************
第二个办法,就是存过啦: 自己写的,很糙,但效果很好
BEGIN
DECLARE recursiveDept VARCHAR(10);
set recursiveDept = in_deptCode;
WHILE recursiveDept != '2000000000' DO
SELECT in_deptCode as initDept,
upperDeptCode as recursiveDept,
sysdate() as updatedDate
from sc_pubcompany where deptCode = recursiveDept;
select upperDeptCode into recursiveDept from sc_pubcompany where deptCode = recursiveDept;
END WHILE;
END
结果是: 1000条递归查询,只花了 7 s。
建议使用存过或者函数, 不建议使用 @迭代变量。