相关博客：
https://blog.csdn.net/qq_16992475/article/details/121180200 mysql 分组查询排序实现
https://blog.csdn.net/qq_16992475/article/details/118344611 树形结构数据封装的几种方法
https://blog.csdn.net/qq_16992475/article/details/121783805 Mysql递归查询子级&添加序号&从子类ID查询所有父类

需求

组织之间存在层级关系，人员可对应多个组织，对应的多个组织也可以存在上下级关系。
查询某个人的组织的时候，需要把这个人对应的所有组织，和对应的每一个组织的所有上级都按序查询出来，并且根据组织id查询时，需要把该组织及该组织的所有子级组织的人员都查询出来。

PersonListDto

public class PersonListDTO {

    private Long id;

   
    private String personCode;

 
    private String name;


    private String position;

    private List<OrganizationReleaseDTO> organizationReleaseDTOList;

    private Date createTime;
}

OrganizationReleaseDTO

public class OrganizationReleaseDTO {
    private Long id;

    private List<String> organizationNameList;
}

PersonMapper.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.anbao.ambientMonitor.mapper.person.PersonMapper">

    <resultMap id="selectOrganizationDataInfo" type="com.anbao.ambientMonitor.data.dto.person.PersonListDTO">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="position" column="position"/>
        <result property="createTime" column="create_time"/>
        <result property="personCode" column="person_code"/>
        <collection property="organizationReleaseDTOList" ofType="abc"
                    select="selectOrganizationIdByPersonId" column="id">
        </collection>
    </resultMap>
    <resultMap id="abc" type="com.anbao.ambientMonitor.data.dto.organization.OrganizationReleaseDTO">
        <result property="id" column="id"/>
        <collection property="organizationNameList" javaType="java.util.ArrayList"
                    ofType="java.lang.String"
                    select="selectOrganizationNameList" column="id">
        </collection>
    </resultMap>

    <select id="selectOrganizationIdByPersonId" resultMap="abc">
        SELECT
            o.id,
            o.parent_id,
            o.organization_name
        FROM
            organization_person op
                RIGHT JOIN organization o ON o.id = op.organization_id
        WHERE
            op.person_id = #{id}
          AND o.is_del = 0
    </select>
	
	<!-- 查询指定组织的上级一直到顶级并按序排列，(顶级在上面，一直到所查询的指定层级，这种就可以实现类似目录的结构) -->
    <select id="selectOrganizationNameList" resultType="java.lang.String">
        SELECT
            T2.organization_name
        FROM
            (SELECT @r := #{id}, @l := 0) vars,
            (SELECT
	             @r AS _id,
	             (SELECT @r := parent_id FROM organization WHERE id = _id) AS parent_id,
	             @l := @l + 1 AS lvl
             FROM
                 organization h
             WHERE @r != 0
             ) T1
        JOIN organization T2 ON T1._id = T2.id
        ORDER BY T1.lvl DESC
    </select>

	<!-- 下面后2个拼出来的表其实可以单独使用，可以查询指定层级的所有下级,查询出来后，再构建出层级关系，
	     构建层级关系的方法，可参考：树形结构数据封装的几种方法 https://blog.csdn.net/qq_16992475/article/details/118344611 -->
    <select id="queryPageHasOrganizationId" resultMap="selectOrganizationDataInfo">
        SELECT DISTINCT
            p.id,
            p.name,
            p.position,
            p.create_time,
            p.person_code
        FROM
            person p
            <if test="params.organizationId != null and params.organizationId != ''">
                INNER JOIN organization_person op ON op.person_id = p.id
                INNER JOIN organization o ON o.id = op.organization_id
                ,(SELECT @ids := #{params.organizationId}) b
                ,(SELECT 
               		@ids AS _ids,
               		(SELECT 
               			@ids := GROUP_CONCAT(id) 
               		 FROM 
               		 	organization	
               		 WHERE 
               		 	FIND_IN_SET(parent_id, @ids)
               		) AS cids 
                  FROM organization 
                  WHERE @ids IS NOT NULL 
                 ) a
            </if>
        <where>
            p.merchant_id = #{params.merchantId}
            <if test="params.organizationId != null and params.organizationId != ''">
                AND FIND_IN_SET(o.id, a._ids)
            </if>
            <if test="params.name != null and params.name != ''">
                AND p.name LIKE CONCAT( '%', #{params.name}, '%' )
            </if>
            AND p.is_del = 0
            <if test="params.organizationId != null and params.organizationId != ''">
                AND o.is_del = 0
            </if>
        </where>
        ORDER BY p.create_time DESC
    </select>

</mapper>

OrganizationMaper.xml

再看一个OrganizationMaper.xml中的一个示例：

<select id="selectRegionIdByOrganizationId" resultType="java.lang.Long">
    SELECT
        orr.region_id
    FROM
        organization_region orr
            INNER JOIN (
            SELECT
                mr.id
            FROM
                (SELECT @ids := 0) b,
                (SELECT
                      @ids AS _ids,
                      (SELECT 
                       		@ids := GROUP_CONCAT(id) 
                       FROM 
                       		merchant_region 
                       WHERE 
                      		FIND_IN_SET(parent_id, @ids)
                      ) AS cids
                 FROM 
                 	 merchant_region
                 WHERE
                     @ids IS NOT NULL) c1,
                merchant_region mr
                LEFT JOIN merchant_user_region mur ON mr.id = mur.merchant_region_id
            where
                FIND_IN_SET(mr.id, c1._ids)
              	AND mr.is_del = 0
              	AND mur.merchant_sub_user_id = #{merchantSubUserId}
            ORDER BY mr.create_time DESC) mr ON mr.id = orr.region_id
    WHERE
        orr.organization_id = #{id}
</select>