mysql 函数tree状

// 子节点的查询

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)>0;
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(1000)
BEGIN
DECLARE sTemp VARCHAR(1000);
DECLARE sTempPrt VARCHAR(1000);
 
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)>0;
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);

 

转载于:https://www.cnblogs.com/chengyangyang/p/10489270.html