mysql_ztree 递归查询

 

数据库表如下：

 

SET GLOBAL log_bin_trust_function_creators = 1;

drop FUNCTION if EXISTS getChildLst;

CREATE FUNCTION `getChildLst`(rootId INT) 
    RETURNS varchar(1000) 
    BEGIN 
        DECLARE sTemp VARCHAR(1000); 
        DECLARE sTempChd VARCHAR(1000); 
        SET sTemp = '^'; 
        SET sTempChd =cast(rootId as CHAR); 
        WHILE sTempChd is not null DO 
            SET sTemp = concat(sTemp,',',sTempChd); 
            SELECT group_concat(id) INTO sTempChd FROM tb_sys_dept 
    where FIND_IN_SET(pid,sTempChd)>0; 
        END WHILE; 
        RETURN sTemp; 
END ;

 

select td.ID,td.`NAME`,td.PID,td.LEV from tb_sys_dept td where FIND_IN_SET(td.ID, getChildLst(1));