mybatis多对多查询分页问题解决

第一种方案：

<resultMap type="SysUser" id="SysUserResultForList">
    <id     property="userId"       column="user_id"      />
    <result property="deptId"       column="dept_id"      />
    <result property="userName"     column="user_name"    />
    <result property="nickName"     column="nick_name"    />
    <result property="email"        column="email"        />
    <result property="phonenumber"  column="phonenumber"  />
    <result property="sex"          column="sex"          />
    <result property="avatar"       column="avatar"       />
    <result property="password"     column="password"     />
    <result property="status"       column="status"       />
    <result property="delFlag"      column="del_flag"     />
    <result property="loginIp"      column="login_ip"     />
    <result property="loginDate"    column="login_date"   />
    <result property="createBy"     column="create_by"    />
    <result property="createTime"   column="create_time"  />
    <result property="updateBy"     column="update_by"    />
    <result property="updateTime"   column="update_time"  />
    <result property="remark"       column="remark"       />
    <association property="dept"    column="dept_id" javaType="SysDept" resultMap="deptResult" />
    <collection property="roles" 	ofType="SysRole" 	column="user_id" select="selectUserRole"/>
    <collection  property="posts"   ofType="SysPost" 	column="{userId=user_id,postId=post_id}" select="selectUserPost"/>
</resultMap>
<resultMap id="RoleResult" type="SysRole">
    <id     property="roleId"       column="role_id"        />
    <result property="roleName"     column="role_name"      />
    <result property="roleKey"      column="role_key"       />
</resultMap>
<select id="selectUserRole" resultMap="RoleResult">
    select r.role_id, r.role_name, r.role_key from sys_user_role ur
    left join sys_role r on r.role_id = ur.role_id where ur.user_id=#{user_id}
</select>

<select id="selectUserPost" resultMap="PostResult" parameterType="SysUser">
    select ps.post_id,p.post_name from sys_user_post ps left join sys_post p on ps.post_id = p.post_id
    where ps.user_id=#{userId}
    <if test="postId != null and postId != ''">
        AND ps.post_id in
        <foreach collection="postId.split(',')" item="postId" open="(" separator="," close=")">
            #{postId}
        </foreach>
    </if>
</select>

resultMap>>SysUserResultForList中roles中用column字段属性去其查询的select中把对应的值传递到子查询中。同样的posts也是，而且在column中能传递多个参数到子查询中。

第二种方案：

SELECT
	u.*,
	d.officeName,
	r.roleName,
	p.postName 
FROM
	sys_user u
	LEFT JOIN (
	SELECT
		rc.user_id,
		GROUP_CONCAT( rc.role_id ) AS roleid,
		GROUP_CONCAT( rc.role_name ) AS roleName 
	FROM
		(
		SELECT
			ur.user_id,
			r.role_id,
			r.role_name 
		FROM
			sys_user_role ur
			LEFT JOIN sys_role r ON ur.role_id = r.role_id 
		) rc 
	GROUP BY
		rc.user_id 
	) r ON u.user_id = r.user_id
	LEFT JOIN (
	SELECT
		pc.user_id,
		GROUP_CONCAT( pc.post_id ) AS postId,
		GROUP_CONCAT( pc.post_name ) AS postName 
	FROM
		(
		SELECT
			up.user_id,
			p.post_id,
			p.post_name 
		FROM
			sys_user_post up
			LEFT JOIN sys_post p ON up.post_id = p.post_id 
		) pc 
	GROUP BY
		pc.user_id 
	) p ON u.user_id = p.user_id
	LEFT JOIN (
	SELECT
		oc.user_id,
		GROUP_CONCAT( oc.office_id ) AS officeIds,
		GROUP_CONCAT( oc.office_name ) AS officeName 
	FROM
		(
		SELECT
			uo.user_id,
			o.office_id,
			o.office_name 
		FROM
			sys_user_office uo
			LEFT JOIN sys_office o ON uo.office_id = o.office_id 
		) oc 
	GROUP BY
		oc.user_id 
	) d ON u.user_id = d.user_id 
WHERE
	(
	FIND_IN_SET( '2', officeIds ) 
	OR FIND_IN_SET( '3', officeIds ))

把分页查询的sql中，用多的一方进行group by主表中共同的字段，然后通过关联查询出需要的数据。