MySql不支持函数递归查询的平替方法

啊凉大魔王1

已于 2023-10-30 10:41:36 修改

阅读量175

点赞数

文章标签： mysql 数据库

于 2023-10-30 10:35:01 首次发布

本文链接：https://blog.csdn.net/weixin_43264955/article/details/134113808

版权

MySql不支持函数递归查询的平替方法

`1、创建存储过程，递归查询出该部门的一级父部门`

drop procedure IF EXISTS querySonStaffIdInfo;

DELIMITER //
CREATE PROCEDURE querySonStaffIdInfo(IN currentDeptId INT,out result varchar(255))
BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE level INT;
    DECLARE ppid INT;
    DECLARE lastDeptId INT;

    -- 循环查询子部门
    WHILE NOT done DO
        -- 查询下一个部门
        SELECT (select parent_id from auth_dept where id = ad.parent_id) ppid,level,parent_id,id INTO ppid,level,currentDeptId,lastDeptId FROM auth_dept ad WHERE id = currentDeptId;
        IF lastDeptId is null or level = 1 or ppid = -1 THEN
            SET done = 1;
        END IF;
    END WHILE;

    -- 查询结果
    SELECT dept_name into result  FROM auth_dept ad WHERE id = lastDeptId;

END;
//
DELIMITER ;

`2、创建函数调用存储过程返回结果`

DROP FUNCTION IF EXISTS querySonStaffIdInfo;

DELIMITER ;;
CREATE FUNCTION querySonStaffIdInfo(deptId INT)
RETURNS VARCHAR(255)
READS SQL DATA
BEGIN
    CALL querySonStaffIdInfo(deptId, @result);
    return (select @result);
END
;;

`3、调用函数查询结果`

select querySonStaffIdInfo(28);

如果mysql版本支持函数递归查询

DROP FUNCTION IF EXISTS querySonStaffIdInfo;

DELIMITER ;;
CREATE FUNCTION querySonStaffIdInfo(deptId INT)
RETURNS VARCHAR(255)
READS SQL DATA
BEGIN
	DECLARE deptname VARCHAR(255);
	DECLARE parentDepartmentId INT;
	DECLARE level INT;
	DECLARE ppid INT;

	SELECT parent_id, dept_name, level, (select parent_id from auth_dept where id = ad.parent_id) INTO parentDepartmentId, deptname, level, ppid FROM auth_dept ad WHERE ad.id = deptId;

	if level = 1 or ppid = -1 then
	    return deptname;
	else
        RETURN querySonStaffIdInfo(parentDepartmentId);
	end if;
END
;;



select querySonStaffIdInfo(28);