Mysql带层级(父子级)的递归查询

需求

组织之间存在层级关系，人员可对应多个组织，对应的多个组织也可以存在上下级关系。 查询某个人的组织的时候，需要把这个人对应的所有组织，和对应的每一个组织的所有上级都按序查询出来，并且根据组织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

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>