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

相关博客：
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 organizationReleaseDTOList;

    private Date createTime;
}

OrganizationReleaseDTO

public class OrganizationReleaseDTO {
    private Long id;

    private List organizationNameList;
}

PersonMapper.xml

    
        
        
        
        
        
        
        
    
    
        
        
        
    

    
        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
            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 DISTINCT
            p.id,
            p.name,
            p.position,
            p.create_time,
            p.person_code
        FROM
            person p
            
                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
            
        
            p.merchant_id = #{params.merchantId}
            
                AND FIND_IN_SET(o.id, a._ids)
            
            
                AND p.name LIKE CONCAt( '%', #{params.name}, '%' )
            
            AND p.is_del = 0
            
                AND o.is_del = 0
            
        
        ORDER BY p.create_time DESC
    



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

    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}