mysql每增加一级增加三位，同级则累加1, 100的直接下级为100100、100101，100101的直接下级为100101100、100101101，依次类推；

本文链接：https://blog.csdn.net/qq_38765404/article/details/88659843

表结构：

CREATE TABLE `sys_dept` (
  `dept_id` bigint(16) NOT NULL COMMENT '部门id',
  `parent_id` bigint(16) DEFAULT '0' COMMENT '父部门id',
  `jbdm` varchar(128) NOT NULL COMMENT '级别代码，每级从100开始，即需保证同级部门不能超过900个，方便统计用',
  `dept_name` varchar(30) DEFAULT '' COMMENT '部门名称',
  `order_num` int(4) DEFAULT '0' COMMENT '显示顺序',
  `leader` varchar(20) DEFAULT '' COMMENT '负责人',
  `phone` varchar(11) DEFAULT '' COMMENT '联系电话',
  `email` varchar(50) DEFAULT '' COMMENT '邮箱',
  `status` char(1) DEFAULT '0' COMMENT '部门状态（0正常 1停用）',
  `del_flag` char(1) DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  `create_by` varchar(64) DEFAULT '' COMMENT '创建者',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) DEFAULT '' COMMENT '更新者',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`dept_id`) USING BTREE,
  KEY `index_dept_jbdm` (`jbdm`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='部门表';

表测试数据：

-- ----------------------------
-- 初始化-部门表数据
-- ----------------------------
insert into sys_dept values(1812121212120000,  0,   '100','LWS科技',   0, 'Lws', '15888888888', 'zs@qq.com', '0', '0', 'admin', '2018-03-16 11-33-00', 'zs', '2018-03-16 11-33-00');
insert into sys_dept values(1812121212120001,  1812121212120000, CONCAT((select a.j from(select jbdm j from sys_dept where dept_id=1812121212120000)a),(select a.j from(select (count(*)+100)j from sys_dept where parent_id=1812121212120000)a)),'深圳总公司', 1, '张三', '15888888888', 'zs@qq.com', '0', '0', 'admin', '2018-03-16 11-33-00', 'zs', '2018-03-16 11-33-00');
insert into sys_dept values(1812121212120002,  1812121212120000, CONCAT((select a.j from(select jbdm j from sys_dept where dept_id=1812121212120000)a),(select a.j from(select (count(*)+100)j from sys_dept where parent_id=1812121212120000)a)),'长沙分公司', 2, '张三', '15888888888', 'zs@qq.com', '0', '0', 'admin', '2018-03-16 11-33-00', 'zs', '2018-03-16 11-33-00');

mysql :

SELECT
	IFNULL(
		MAX( IF ( CAST( jbdm AS SIGNED ) = 0, NULL, CAST( jbdm AS SIGNED ) ) ) + 1,
		( SELECT concat( jbdm, 100 ) FROM sys_dept WHERE dept_id = 1812121212120000)
		) 
	FROM
		sys_dept 
WHERE
	parent_id = 1812121212120000

mybatis mapper:

    <select id="generateNewJbdmByParentId" parameterType="Long" resultType="String">
		SELECT IFNULL(
			MAX(if(CAST(jbdm AS SIGNED)=0,null,CAST(jbdm AS SIGNED)))+1,
			(SELECT concat(jbdm,100) from sys_dept WHERE dept_id=#{parentId})
		)  FROM sys_dept WHERE parent_id=#{parentId}
	</select>

sql :

SELECT
	CONCAT(
		( SELECT a.j FROM ( SELECT jbdm j FROM sys_dept WHERE dept_id = 1812121212120000 ) a ),
	( SELECT a.j FROM ( SELECT ( count( * ) + 100 ) j FROM sys_dept WHERE parent_id = 1812121212120000 ) a ) 
	)