mysql怎么递归查询下级_mysql递归查询上下级

最近做项目遇到地区查询问题，涉及递归，在java代码中处理发现效率较低，遂考虑使用sql递归

贴上一张表结构：

一、查询下级方法

CREATE FUNCTION getChildrenOrg (orgid INT) RETURNS VARCHAR (4000)

BEGIN

DECLARE oTemp VARCHAR (4000);

DECLARE oTempChild VARCHAR (4000);

SET oTemp = '';

SET oTempChild = CAST(orgid AS CHAR);

WHILE oTempChild IS NOT NULL DO

SET oTemp = CONCAT(oTemp, ',', oTempChild);

SELECT

GROUP_CONCAT(areaid) INTO oTempChild

FROM

area

WHERE

FIND_IN_SET(parentid, oTempChild) > 0;

END

WHILE;

RETURN oTemp;

END

调用方法(查询areaid为620000(甘肃)的地区的全部下级)

SELECT

*

FROM

area

WHERE

FIND_IN_SET(

areaid,

getChildrenOrg (620000)

)

结果：

一、查询上级方法

SELECT *

FROM (

SELECT

@r AS _id,

(SELECT @r := parentid FROM area WHERE areaid = _id) AS parent_id,

@l := @l + 1 AS lvl

FROM

(SELECT @r := 330324, @l := 0) vars,

area h

WHERE @r <> 0) T1

JOIN area T2

ON T1._id = T2.areaid

ORDER BY areaid;

注意其中的330324就是子节点areaid

结果如下：