需求:通过dept_id查询出所有的父级;
需要用到递归。mysql函数创建:
CREATE FUNCTION `getParentList`(rootId varchar(100))
RETURNS varchar(1000)
BEGIN
DECLARE fid varchar(100) default '';
DECLARE str varchar(1000) default rootId;
WHILE rootId is not null do
SET fid =(SELECT parent_id FROM basic_dept WHERE dept_id = rootId);
IF fid is not null THEN
SET str = concat(str, ',', fid);
SET rootId = fid;
ELSE
SET rootId = fid;
END IF;
END WHILE;
return str;
END
SELECT
*
FROM
basic_dept bc
WHERE
FIND_IN_SET(dept_id,getParentList('124'))
getParentList('124')返回的是以逗号分隔的dept_id;
FIND_IN_SET(XX,XX) 这个函数的意思是查询表dept_id字段有后面这个参数的值的所有数据!
结果: