// 子节点的查询

CREATE DEFINER = `root`@`%` FUNCTION `getDeptChildList`(rootId BIGINT)

 RETURNS longtext

    DETERMINISTIC

BEGIN

DECLARE sTemp LONGTEXT;

DECLARE sTempChd LONGTEXT;

SET sTemp = '';

SET sTempChd =cast(rootId as CHAR);

loop1:LOOP

SELECT group_concat(id) INTO sTempChd FROM sys_dept where FIND_IN_SET(pid,sTempChd)>;

IF sTempChd is not null THEN

SET sTemp = concat(sTemp,',',sTempChd);

ELSE

LEAVE loop1;

END IF;

END LOOP;

SET sTemp = TRIM(',' FROM sTemp);

RETURN sTemp;

END;

select A.* from sys_dept A inner join (select getDeptChildList('1067246875800000066') as idArr) TEMP where FIND_IN_SET(A.id, TEMP.idArr);

CREATE FUNCTION `getFatherLst`(rootId INT)

RETURNS varchar()

BEGIN

DECLARE sTemp VARCHAR();

DECLARE sTempPrt VARCHAR();

SET sTemp = '';

SET sTempPrt = cast(rootId as CHAR);

loop1:LOOP

SELECT group_concat(father_id) INTO sTempPrt FROM tbl_tree where FIND_IN_SET(id,sTempChd)>;

IF sTempPrt is not null THEN

SET sTemp = concat(sTemp,',',sTempPrt);

ELSE

LEAVE loop1;

END IF;

END LOOP;

SET sTemp = TRIM(',' FROM sTemp);

RETURN sTemp;

END

select A.* from tbl_tree A inner join (select getFatherLst(15000) as idArr) TEMP where FIND_IN_SET(A.id, TEMP.idArr);