产品需求如下：

那么怎么实现看代码：

返回的数据结构如下

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;
import java.util.List;

/**
 * 人员分类表返回数据VO
 *
 * @author zipeng.yuan
 * @date 2021-12-01 22:21:44
 */
@Data
public class PersonCategoryListVO implements Serializable {

    private static final long serialVersionUID = 1L;


    /**
     * 主键
     * 字段名字：id
     */
    @ApiModelProperty(value = "主键")
    private Integer id;

    /**
     * 父id
     * 字段名字：parent_id
     */
    @ApiModelProperty(value = "父id")
    private Integer parentId;

    /**
     * 人员类别名称
     * 字段名字：name
     */
    @ApiModelProperty(value = "人员类别名称")
    private String name;

    /**
     * 人员总数
     */
    @ApiModelProperty(value = "人员总数")
    private Integer total;

    @ApiModelProperty(value = "人才库子级")
    private List<PersonCategoryListVO> children;

}

xml代码如下：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.talebase.base.dao.person.PersonCategoryDao">

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap id="BaseResultMap" type="com.talebase.base.entity.person.PersonCategoryDO">
        <result column="id" jdbcType="INTEGER" property="id"/>
        <result column="parent_id" jdbcType="INTEGER" property="parentId"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="type" jdbcType="INTEGER" property="type"/>
        <result column="top_flag" jdbcType="INTEGER" property="topFlag"/>
        <result column="note" jdbcType="VARCHAR" property="note"/>
        <result column="sort" jdbcType="INTEGER" property="sort"/>
        <result column="del_flag" jdbcType="INTEGER" property="delFlag"/>
        <result column="update_time" jdbcType="TIMESTAMP" property="updateTime"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="update_id" jdbcType="INTEGER" property="updateId"/>
        <result column="create_id" jdbcType="INTEGER" property="createId"/>
    </resultMap>

    <!-- 可根据自己的需求，是否要使用 -->
    <resultMap id="categoryDeptTreeMap" type="com.talebase.base.vo.person.PersonCategoryListVO">
        <result column="id" jdbcType="INTEGER" property="id"/>
        <result column="parent_id" jdbcType="INTEGER" property="parentId"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
        <result column="type" jdbcType="INTEGER" property="type"/>
        <result column="top_flag" jdbcType="INTEGER" property="topFlag"/>
        <result column="note" jdbcType="VARCHAR" property="note"/>
        <result column="sort" jdbcType="INTEGER" property="sort"/>
        <result column="del_flag" jdbcType="INTEGER" property="delFlag"/>
        <collection property="children"
                    ofType="com.talebase.base.vo.person.PersonCategoryListVO"
                    column="id"
                    select="getChildren">
        </collection>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
            id,
            parent_id,
            name,
            type,
            top_flag,
            note,
            sort,
            del_flag,
            update_time,
            create_time,
            update_id,
            create_id
        </sql>

    <select id="listPersonCategoryPage" resultMap="categoryDeptTreeMap">
        SELECT
            pc.id,
            pc.parent_id,
            pc.name,
            pc.type,
            pc.top_flag,
            pc.note,
            pc.sort,
            pc.del_flag,
            (
                SELECT
                    COUNT(0)
                FROM
                    person_category_rel pcr
                WHERE
                    pcr.person_category_id = pc.id
            ) total
        FROM
            person_category pc
        <where>
            pc.parent_id = 0
        </where>
        ORDER BY pc.create_time
    </select>

    <select id="getChildren" resultMap="categoryDeptTreeMap">
        SELECT
            pc.id,
            pc.parent_id,
            pc.name,
            pc.type,
            pc.top_flag,
            pc.note,
            pc.sort,
            pc.del_flag,
            (
                SELECT
                    COUNT(0)
                FROM
                    person_category_rel pcr
                WHERE
                    pcr.person_category_id = pc.id
            ) total
        FROM
            person_category pc
        <where>
            <if test="id != null">
                and pc.parent_id = #{id}
            </if>
        </where>
        ORDER BY pc.create_time
    </select>


</mapper>