Java查询每个部门总人数（包含每个部门层级的总数）

绝地灬酷狼 2023-10-13 14:36 68阅读 0赞

需求：最近需要统计每个部门的用户数量，最开始想的比较简单，通过部门id去查询，结果忘记了部门下面有子集部门，这样通过一般的条件查询就只能查出当前部门的人数。而不能查询当前部门下面包含了子集部门的人数，查询方法如下：

1.用递归的方式查询出部门树

2.在递归里面通过部门节点路径来**后模糊查询**

3.完整代码分享

**MYSQL库表设计：**

**用户表和角色表就行了，用户角色关联表这里用不上**

CREATE TABLE `org` (
      `id` bigint unsigned NOT NULL COMMENT '主键id',
      `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '组织机构名称',
      `parent_id` bigint unsigned NOT NULL DEFAULT '0' COMMENT '上级机构id',
      `level` smallint unsigned NOT NULL DEFAULT '1' COMMENT '树节点层级',
      `node_path` varchar(512) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '节点路径',
      `status` tinyint unsigned NOT NULL DEFAULT '1' COMMENT '状态值;启用1是0否',
      `sort_no` smallint unsigned NOT NULL DEFAULT '10' COMMENT '排序号;规则升序',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='组织机构表';

CREATE TABLE `user` (
      `id` bigint unsigned NOT NULL COMMENT '主键',
      `username` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '用户名',
      `cname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '中文名',
      `password` varchar(40) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '密码',
      `id_number` char(18) CHARACTER SET utf8mb4 COLLATE utf8mb4_icelandic_ci DEFAULT NULL COMMENT '身份证号',
      `phone_number` char(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '手机号',
      `status` tinyint unsigned DEFAULT '1' COMMENT '状态值;1启用0禁用',
      `updated` datetime DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
      `org_id` bigint unsigned DEFAULT '1000' COMMENT '机构id',
      `sort` smallint DEFAULT '10' COMMENT '排序号',
      PRIMARY KEY (`id`) USING BTREE,
      UNIQUE KEY `uk_username` (`username`) USING BTREE,
      UNIQUE KEY `uk_phone_number` (`phone_number`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='用户表';

**主要代码：**

/**
         * 获取组织机构树
         * @return
         */
        public List<OrgTreeNode> tree() {
            List<Org> all = this.loadAll();
            if (CollectionUtil.isEmpty(all)) {
                return Collections.emptyList();
            }
    
            List<OrgTreeNode> roots = getRoot(all);
            if (CollectionUtil.isEmpty(roots)) {
                return Collections.emptyList();
            }
            for (OrgTreeNode root : roots) {
                root.setChildren(getChildren(all, root.getId()));
            }
            return roots;
        }

/**
         * 递归查询子节点
         *
         * @param all      所有节点
         * @param parentId 父节点id
         * @return
         */
        private List<OrgTreeNode> getChildren(List<Org> all, Long parentId) {
            List<OrgTreeNode> children = all.stream()
                    .filter(org -> Objects.equals(org.getParentId(), parentId))
                    .map(org -> {
                        OrgTreeNode node = new OrgTreeNode();
                        BeanUtils.copyProperties(org, node);
                        node.setChildren(getChildren(all, node.getId()));
                        return node;
                    }).collect(Collectors.toList());
            //查询当前部门用户人数
            for (int i = 0; i < children.size(); i++) {
                //根据部门节点查询当前部门用户数量
                int userNum = baseMapper.countUserNumOrgId(children.get(i).getNodePath());
                children.get(i).setEstablishmentNumber(userNum);
            }
            if (CollectionUtil.isEmpty(children)) {
                return Collections.emptyList();
            } else {
                return children;
            }
        }

/**
         * 根据部门节点统计当前部门用户数量
         * @param orgNodePath
         * @return
         */
        @Select("SELECT COUNT(*) AS total_count FROM USER u LEFT JOIN org o ON u.org_id = o.id WHERE o.node_path LIKE '${orgNodePath}%' AND o.id IS NOT NULL;")
        int countUserNumOrgId(@Param("orgNodePath") String orgNodePath);