祖籍列表id拼接

最新推荐文章于 2025-05-03 10:56:02 发布

追梦者123

最新推荐文章于 2025-05-03 10:56:02 发布

阅读量136

点赞数

文章标签： java 数据库前端

本文链接：https://blog.csdn.net/WYT11/article/details/132832714

版权


CREATE DEFINER=`root`@`%` FUNCTION `sys_organization_getAncestors`(deptId varchar(36)) RETURNS varchar(1000) CHARSET utf8
    DETERMINISTIC
BEGIN
    DECLARE parentDeptId varchar(36) default '';  -- 父部门id
    declare parentDeptName varchar(100) default ''; -- 父部门名称
    declare currentDeptName varchar(100) default ''; -- 当前部门名称
    declare retStr varchar(1000) default '';    --  返回字符串（部门1/部门1的子部门1/部门1的子部门2）
		declare deptName varchar(2000) default '';

		## 获取当前部门名称
    if deptId is not null and length(deptId) > 0 then
        set currentDeptName = (select ifnull(ancestors, '') from sys_organization where uuid = deptId);
    else
        return retStr;
    end if;

    WHILE deptId is not null and deptId <> '1'
        do
            SET parentDeptId = (SELECT parent_id FROM sys_organization WHERE uuid = deptId);
            IF parentDeptId is not null and parentDeptId <> '1'   THEN
                set parentDeptName = (select ancestors from sys_organization td where td.uuid = parentDeptId);

                if parentDeptName is not null then
                    set deptName = concat(parentDeptName,'/', deptName);
                end if;

                SET deptId = parentDeptId;
            ELSE
                SET deptId = parentDeptId;
            END IF;
        END WHILE;

    -- 将当前部门名称 拼接到最后
    set retStr = concat(deptName,currentDeptName);
    return retStr;
END