// 子节点的查询 CREATE DEFINER = `root`@`%` FUNCTION `getDeptChildList`(rootId BIGINT) RETURNS longtext    DETERMINISTICBEGINDECLARE sTemp LONGTEXT;DECLARE sTempChd LONGTEXT; SET sTemp = '';SET sTempChd =cast(rootId as CHAR); loop1:LOOPSELECT group_concat(id) INTO sTempChd FROM sys_dept where FIND_IN_SET(pid,sTempChd)>0;IF sTempChd is not null THENSET sTemp = concat(sTemp,',',sTempChd);ELSELEAVE 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(1000)BEGINDECLARE sTemp VARCHAR(1000);DECLARE sTempPrt VARCHAR(1000); SET sTemp = '';SET sTempPrt = cast(rootId as CHAR); loop1:LOOPSELECT group_concat(father_id) INTO sTempPrt FROM tbl_tree where FIND_IN_SET(id,sTempChd)>0;IF sTempPrt is not null THENSET sTemp = concat(sTemp,',',sTempPrt);ELSELEAVE 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);