数据移植时递归运算查询部门及其下级所有部门的问题

今天在做项目的数据移植时遇到一个问题。

问题描述：

现有两张表，一张是原数据表，一张是要进行移植的数据表。

原数据表：

表名：组织-部门基本信息表

建表语句：

CREATE TABLE `org_dept_base` (
  `dept_id` bigint NOT NULL COMMENT '部门ID',
  `code` varchar(80) NOT NULL COMMENT '编号',
  `name` varchar(256) DEFAULT NULL COMMENT '名称',
  `name_short` varchar(32) DEFAULT NULL COMMENT '简称',
  `name_en` varchar(256) DEFAULT NULL COMMENT '英文名称',
  `name_short_en` varchar(256) DEFAULT NULL COMMENT '英文简称',
  `ucode` varchar(80) DEFAULT NULL COMMENT '统一代码',
  `logo_file` bigint DEFAULT NULL COMMENT 'logo文件',
  `type` tinyint DEFAULT NULL COMMENT '组织类型##ref:数据成员类型',
  `org_status` tinyint DEFAULT NULL COMMENT '组织状态##ref:启用状态',
  `create_date` date DEFAULT NULL COMMENT '成立日期',
  `manager_name` varchar(128) DEFAULT NULL COMMENT '组织负责人',
  `offic_addr` varchar(160) DEFAULT NULL COMMENT '办公地址',
  `office_tel` varchar(40) DEFAULT NULL COMMENT '办公电话',
  `path` varchar(256) DEFAULT NULL COMMENT '默认归属路径',
  `ext_info` varchar(1024) DEFAULT NULL COMMENT '扩展信息',
  `parent_id` bigint NOT NULL COMMENT '部门ID',
  `cmp_id` bigint NOT NULL COMMENT '公司ID',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_user` bigint DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `update_user` bigint DEFAULT NULL COMMENT '修改人',
  `isdel` tinyint DEFAULT NULL COMMENT '是否删除',
  `remark` varchar(1024) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='组织-部门基本信息表';

要移植的数据表：

表名：数据成员归属关系信息表

建表语句：

CREATE TABLE `data_relation` (
  `relat_id` bigint NOT NULL AUTO_INCREMENT COMMENT '关系ID',
  `data_id1` bigint NOT NULL COMMENT '成员数据id',
  `data_type` tinyint DEFAULT NULL COMMENT '成员数据类型##ref:数据成员类型',
  `relat_type` int DEFAULT NULL COMMENT '数据关系类型*##ref:数据关系类型',
  `parent_id` bigint NOT NULL COMMENT '数据归属上级ID',
  `path` varchar(256) DEFAULT NULL COMMENT '归属路径',
  `start_date` datetime DEFAULT NULL COMMENT '开始日期##JSONField:yyyy-MM-dd HH:mm:ss',
  `end_date` datetime DEFAULT NULL COMMENT '结束日期##JSONField:yyyy-MM-dd HH:mm:ss',
  `status` tinyint DEFAULT NULL COMMENT '归属状态',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `create_user` bigint DEFAULT NULL COMMENT '创建人',
  `update_time` datetime DEFAULT NULL COMMENT '修改时间',
  `update_user` bigint DEFAULT NULL COMMENT '修改人',
  `isdel` tinyint DEFAULT NULL COMMENT '是否删除',
  PRIMARY KEY (`relat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=32767 DEFAULT CHARSET=utf8 COMMENT='数据成员归属关系信息表';

问题1：原表中path(默认归属路径)字段无数据，需要先对其更新数据

规则：path(默认归属路径) = 租户/公司/部门，其中部门可能是一级、二级、三级、四级或多级部门，以三级部门为例，则path=租户ID/公司ID/一级部门ID/二级部门ID/三级部门ID

在这里需要查询出当前部门的ID后，再查询出其上级部门的部门ID，如果上级部门还有上级部门，则需要继续查询出上级部门的部门ID，看到这里我想到了递归算法，于是百度查询了MySql的递归运算，在一番查找后可以发现递归分为向上递归和向下递归，在这里我们要查询部门的上级所有部门所以是采用向上递归

需要用到的函数：

CONCAT_WS():

使用指定的分割符进行字符连接，例如SELECT CONCAT_WS("/","A","B","C");    

 GROUP_CONCAT():

1、功能：将group by产生的同一个分组中的值连接起来，返回一个字符串结果

2、语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )

说明：通过使用distinct可以排除重复值；如果希望对结果中的值进行排序，可以使用order by子句；separator是一个字符串值，缺省为一个逗号。

直接上sql：

update org_dept_base set path = (select CONCAT_WS('/',1001,1,(SELECT GROUP_CONCAT(t.dept_id SEPARATOR '/') FROM (
    SELECT parent_id,dept_id
    FROM ( 
        SELECT 
                @r AS _dept_id, 
                (SELECT @r := parent_id FROM org_dept_base WHERE dept_id = _dept_id) AS dept_parent_id, 
                 @l := @l + 1 AS lvl 
        FROM 
                (SELECT @r := org_dept_base.dept_id , @l := 0) vars, 
                org_dept_base h 
        WHERE @r <> 0) T1 
    JOIN org_dept_base T2 
    ON T1._dept_id = T2.dept_id
order by dept_id
) t)))

将org_dept_base.dept_id替换为具体的部门ID来展示一下内层SQL的运行结果：

如上图发现已经按照我们的需求进行了部门字段的连接

问题二：在原数据表中新增完path(默认归属路径)数据后，按规则向要移植的数据表中进行数据移植

在完成第一步的path更新后这个就简单了，直接上SQL：

INSERT INTO data_relation(relat_id,data_id1,data_type,relat_type,parent_id,path,start_date,end_date,create_time, create_user,update_time,update_user,isdel)
(select nextval("id") as relat_id,dept_id as data_id1,20 as data_type,0 as relat_type,parent_id as parent_id,path as path,NULL,NULL,create_time as create_time,create_user as create_user,update_time as update_time,update_user as update_user,isdel as isdel from org_dept_base)

对此我们数据移植就成功了！！！