树结构

部门结构

部门表结构DDL

CREATE TABLE `sys_dept` (
  `dept_id` BIGINT(20) NOT NULL AUTO_INCREMENT COMMENT '部门id',
  `parent_id` BIGINT(20) DEFAULT '0' COMMENT '父部门id',
  `ancestors` VARCHAR(50) DEFAULT '' COMMENT '祖级列表',
  `dept_name` VARCHAR(100) DEFAULT '' COMMENT '部门名称',
  `order_num` INT(4) DEFAULT '0' COMMENT '显示顺序',
  `leader` VARCHAR(20) DEFAULT NULL COMMENT '负责人',
  `phone` VARCHAR(11) DEFAULT NULL COMMENT '联系电话',
  `email` VARCHAR(50) DEFAULT NULL 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
) ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='部门表'

表数据data

insert into `sys_dept` (`dept_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `email`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`) values('1','0','1','科技有限公司','0',NULL,NULL,NULL,'0','0','',NULL,'',NULL);
insert into `sys_dept` (`dept_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `email`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`) values('2','1','1,2','产品研发部','0',NULL,NULL,NULL,'0','0','',NULL,'',NULL);
insert into `sys_dept` (`dept_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `email`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`) values('3','1','1,3','销售部','0',NULL,NULL,NULL,'0','0','',NULL,'',NULL);
insert into `sys_dept` (`dept_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `email`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`) values('4','1','1,4','财务部','0',NULL,NULL,NULL,'0','0','',NULL,'',NULL);
insert into `sys_dept` (`dept_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `email`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`) values('5','1','1,5','HR人事','0',NULL,NULL,NULL,'0','0','',NULL,'',NULL);
insert into `sys_dept` (`dept_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `email`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`) values('6','2','1,2,6','研发-前端','0',NULL,NULL,NULL,'0','0','',NULL,'',NULL);
insert into `sys_dept` (`dept_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `email`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`) values('7','2','1,2,7','研发-后端','0',NULL,NULL,NULL,'0','0','',NULL,'',NULL);
insert into `sys_dept` (`dept_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `email`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`) values('8','2','1,2,8','UI设计','0',NULL,NULL,NULL,'0','0','',NULL,'',NULL);
insert into `sys_dept` (`dept_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `email`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`) values('9','2','1,2,9','项目经理','0',NULL,NULL,NULL,'0','0','',NULL,'',NULL);
insert into `sys_dept` (`dept_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `email`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`) values('10','3','1,3,10','销售一部','0',NULL,NULL,NULL,'0','0','',NULL,'',NULL);
insert into `sys_dept` (`dept_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `email`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`) values('11','3','1,3,11','销售二部','0',NULL,NULL,NULL,'0','0','',NULL,'',NULL);
insert into `sys_dept` (`dept_id`, `parent_id`, `ancestors`, `dept_name`, `order_num`, `leader`, `phone`, `email`, `status`, `del_flag`, `create_by`, `create_time`, `update_by`, `update_time`) values('12','7','1,2,7,12','Java','0',NULL,NULL,NULL,'0','0','',NULL,'',NULL);

查找该部门以下的所有部门

方法一: 使用自定义函数queryChildrenDept，只需要维护ID, PARENT_ID字段，查询速度慢

queryChildrenDept函数

create
    definer = root@`%` function queryChildrenDept(rootId int) returns varchar(4000)
BEGIN
    DECLARE sTemp VARCHAR(4000);
    DECLARE sTempChd VARCHAR(4000);
    SET sTemp = '$';
    SET sTempChd = cast(rootId AS CHAR);
    WHILE
        sTempChd IS NOT NULL
        DO
            SET sTemp = CONCAT(sTemp, ',', sTempChd);
            SELECT group_concat(dept_id)
            INTO sTempChd
            FROM sys_dept
            WHERE FIND_IN_SET(parent_Id, sTempChd) > 0;
        END WHILE;
    RETURN sTemp;
END;

查询sql

SELECT * FROM sys_dept WHERE FIND_IN_SET(dept_id, queryChildrenDept(2)) > 0 order by dept_id;

方法二: 使用ancestors(祖籍列表)这个字段，需要维护ID, PARENT_ID, ANCESTORS字段，查询速度快

select  * from sys_dept where DEPT_ID = 2 OR DEPT_ID IN (SELECT dept_id FROM sys_dept WHERE FIND_IN_SET(2, ANCESTORS))

查找该部门以上的所有部门

方法一: 使用自定义函数queryParentDept，只需要维护ID, PARENT_ID字段，查询速度慢

queryParentDept()函数

create
    definer = elead@`%` function queryParentDept(rootId int) returns varchar(1000)
BEGIN
    DECLARE sTemp VARCHAR(1000);
    DECLARE sTempPar VARCHAR(1000); 
    SET sTemp = ''; 
    SET sTempPar = rootId; 
    WHILE sTempPar is not null DO 
        IF sTemp != '' THEN
            SET sTemp = concat(sTemp,',',sTempPar);
        ELSE
            SET sTemp = sTempPar;
        END IF;
        SET sTemp = concat(sTemp,',',sTempPar); 
        SELECT group_concat(parent_Id) INTO sTempPar FROM E_ORG_DEPT
				where parent_Id<>id and FIND_IN_SET(id,sTempPar)>0; 
    END WHILE; 
RETURN sTemp; 
END;

查询SQL

SELECT * FROM sys_dept WHERE FIND_IN_SET(dept_id, queryParentDept(12)) order by dept_id

方法二: 使用ancestors(祖籍列表)这个字段，需要维护ID, PARENT_ID, ANCESTORS字段，查询速度快

SELECT * FROM sys_dept WHERE FIND_IN_SET(dept_id, (SELECT ancestors FROM sys_dept WHERE dept_id = 12)) order by length(ancestors);

查找下一级部门

SELECT * FROM sys_dept WHERE parent_id = 1

查找上一级部门

SELECT * FROM sys_dept WHERE dept_id = (SELECT parent_id FROM sys_dept WHERE dept_id = 12)

查找list节点后，转化为Tree结构

• 方法1：使用Java代码

需要区分list里面的顶点是哪一个, 实现起来较为复杂，性能不好

private List<SysDept> buildDeptTree(List<SysDept> depts) {
    // 记录list里面的顶点
    long rootId = -1L;
    // 节点列表（散列表，用于临时存储节点对象）
    Map<Long, SysDept> treeNodes = new HashMap<Long, SysDept>();
    // 根据结果集构造节点列表（存入散列表）
    for (SysDept dept : depts) {
        treeNodes.put(dept.getDeptId(), dept);
    }
    // 构造无序的多叉树
    Set<Map.Entry<Long, SysDept>> treeEntrySet = treeNodes.entrySet();
    for (Map.Entry<Long, SysDept> treeEntry : treeEntrySet) {
        SysDept node = treeEntry.getValue();
        Long pid = node.getParentId();
        if (ObjectUtil.isNotNull(pid)) {
            if(!StringUtils.isEmpty(treeNodes.get(pid))){
                treeNodes.get(pid).getChildren().add(node);
            }else {
                rootId = node.getDeptId();
            }
        }
    }
    final long root = rootId;
    // 过滤出rootId的节点
    return treeNodes.entrySet().stream().map(Map.Entry::getValue).filter(node -> node.getParentId().longValue() == root).collect(Collectors.toList());
}

• 方法2：mybatis标签

直接使用mybatis标签实现，很方便就可以处理

<resultMap type="SysDept" id="SysDeptResult">
	<id     property="deptId"     column="dept_id"     />
	<result property="parentId"   column="parent_id"   />
	<result property="ancestors"  column="ancestors"   />
	<result property="deptName"   column="dept_name"   />
	<result property="orderNum"   column="order_num"   />
	<result property="leader"     column="leader"      />
	<result property="phone"      column="phone"       />
	<result property="email"      column="email"       />
	<result property="status"     column="status"      />
	<result property="delFlag"    column="del_flag"    />
	<result property="parentName" column="parent_name" />
	<result property="createBy"   column="create_by"   />
	<result property="createTime" column="create_time" />
	<result property="updateBy"   column="update_by"   />
	<result property="updateTime" column="update_time" />
</resultMap>
<select id="getChildrenDeptByPid" resultMap="ChildrenDeptResult">
	select * from sys_dept where parent_id=#{pid};
</select>

<resultMap id="ChildrenDeptResult" type="SysDept" extends="SysDeptResult">
	<collection property="children"
				ofType="SysDept"
				select="com.aloneness.dingtalk.approve.mapper.DeptMapper.getChildrenDeptByPid"
				column="dept_id"/>
</resultMap>