连接查询方式

 <!-- 查询用户角色集合 连接查询 -->
    <select id="selectSysRoleByUserId"  parameterType="long" resultType="com.sh.demo.core.entity.SysRoleEntity" >
        SELECT sr.* FROM sys_role sr
        LEFT JOIN sys_user_role se ON sr.role_id=se.role_id
        WHERE se.user_id = #{userId}
    </select>

 <!-- 查询用户角色集合 子查询 -->
< SELECT id = "selectSysMenuByRoleId" parameterType = "Long" resultMap = "SysMenuResult" > 
    < include refid = "selectSysMenuVo" /> 
WHERE
    menu_id IN (
    SELECT
        menu_id 
    FROM
        sys_role_menu 
    WHERE
    role_id = #{roleId} )
</ SELECT>