1、查询当前节点下的所有子节点数据【包括子节点,孙子节点】
演示:
数据库
【部门表】
【员工表】
编写SQL脚本:
/**所有子节点列表*/
delimiter //
CREATE FUNCTION `getChildList`(rootId varchar(100))
RETURNS varchar(2000)
BEGIN
DECLARE str varchar(2000);
DECLARE cid varchar(100);
SET str = '$';
SET cid = rootId;
WHILE cid is not null DO
SET str = concat(str, ',', cid);
SELECT group_concat(id) INTO cid FROM t_base_department where FIND_IN_SET(parent_id, cid) > 0;
END WHILE;
RETURN str;
END
//
/**所有父节点列表*/
delimiter //
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 t_base_department WHERE 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 t_base_employee where depart_id in (select id from t_base_department where FIND_IN_SET(id,getChildList('201732014041505768')));
结果:
select * from t_base_employee where depart_id in (select id from t_base_department where FIND_IN_SET(id,getParentList('201732014041505768')));
结果: