MYSQL根据当前节点查询父子节点

-- 根据部门ID获取父ID 2019-07-08
drop function f_getDepartmentParent;
DELIMITER ;;
CREATE DEFINER=`root`@`locahost` FUNCTION `f_getDepartmentParent`(departmentId BIGINT(20)) RETURNS varchar(1000) CHARSET utf8
BEGIN
DECLARE sParentList varchar(1000);
DECLARE sParentTemp varchar(1000);
SET sParentTemp =cast(departmentId as CHAR);
WHILE sParentTemp is not null DO
IF (sParentList is not null) THEN
SET sParentList = concat(sParentTemp,',',sParentList);
ELSE
SET sParentList = concat(sParentTemp);
END IF;
SELECT
    GROUP_CONCAT(parent_id)
INTO sParentTemp FROM
    t_department
WHERE
    FIND_IN_SET(id, sParentTemp) > 0;
END WHILE;
RETURN sParentList;
END ;;
DELIMITER ;

-- 根据部门ID获取子ID
drop function f_getDepartmentChild;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `f_getDepartmentChild`(departmentId BIGINT(20)) RETURNS varchar(1000) CHARSET utf8
BEGIN
    DECLARE ptemp VARCHAR(1000);

    DECLARE ctemp VARCHAR(1000);

    SET ptemp = '#';

    SET ctemp = departmentId;

    WHILE ctemp IS NOT NULL DO

      SET ptemp = concat(ptemp, ',', ctemp);

      SELECT group_concat(id)

      INTO ctemp

      FROM t_department

      WHERE FIND_IN_SET(parent_id, ctemp) > 0;

    END WHILE;

    RETURN ptemp;

end ;;
DELIMITER ;

 

转载于:https://www.cnblogs.com/it-way/p/11189850.html