单个处理数据祖籍列表层级关系

CREATE DEFINER=`root`@`%` FUNCTION `sys_organization_getAncestorsNames`(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(organization_name, '') 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 organization_name 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

执行完之后在执行
update sys_organization set ancestors_names= sys_organization_getAncestorsNames(uuid);
即可修改表中所有数据 括号中（uuid）为表的主键