联表查询SQL

      <!-- 用户分页列表 -->
    <select id="getUserPage" resultType="com.youlai.system.model.bo.UserBO">
        SELECT
            u.id,
            u.username,
            u.nickname,
            u.mobile,
            u.gender,
            u.avatar,
            u.STATUS,
            d.NAME AS dept_name,
            GROUP_CONCAT( r.NAME ) AS roleNames,
            u.create_time
        FROM
            sys_user u
                LEFT JOIN sys_dept d ON u.dept_id = d.id
                LEFT JOIN sys_user_role sur ON u.id = sur.user_id
                LEFT JOIN sys_role r ON sur.role_id = r.id
        <where>
            u.deleted = 0 AND u.username != 'root'
            <if test='queryParams.keywords!=null and queryParams.keywords.trim() neq ""'>
                AND (
                    u.username LIKE CONCAT('%',#{queryParams.keywords},'%')
                    OR u.nickname LIKE CONCAT('%',#{queryParams.keywords},'%')
                    OR u.mobile LIKE CONCAT('%',#{queryParams.keywords},'%')
                    )
            </if>
            <if test='queryParams.status!=null'>
                AND u.status = #{queryParams.status}
            </if>
            <if test='queryParams.deptId!=null'>
                AND concat(',',concat(d.tree_path,',',d.id),',') like concat('%,',#{queryParams.deptId},',%')
            </if>
        </where>
        GROUP BY u.id
    </select>
                                                                             
                                                                             
 <!-- 获取路由列表 -->
    <select id="listRoutes" resultMap="RouteMap">
        SELECT
           t1.id,
           t1.name,
           t1.parentId,
           t1.path,
           t1.component,
           t1.icon,
           t1.sort,
           t1.visible,
           t1.redirect,
           t1.type,
           t3.code
        FROM
            sys_menu t1
                 LEFT JOIN sys_role_menu t2 ON t1.id = t2.menuId
                 LEFT JOIN sys_role t3 ON t2.roleId = t3.id
        WHERE
            t1.type != '${@com.youlai.system.common.enums.MenuTypeEnum@BUTTON.getValue()}'
        ORDER BY t1.sort asc
    </select>

    <!-- 获取角色拥有的权限集合 -->
    <select id="listRolePerms" resultType="java.lang.String">
        SELECT
            DISTINCT t1.perm
        FROM
            sys_menu t1
                INNER JOIN sys_role_menu t2 ON t1.id = t2.menuId
                INNER JOIN sys_role t3 ON t3.id = t2.roleId
        WHERE
            t1.type = '${@com.youlai.system.common.enums.MenuTypeEnum@BUTTON.getValue()}'
          AND t1.perm IS NOT NULL
          <choose>
            <when test="roles!=null and roles.size()>0">
                AND t3.CODE IN
                <foreach collection="roles" item="role" separator="," open="(" close=")">
                    #{role}
                </foreach>
            </when>
            <otherwise>
                AND t1.id = -1
            </otherwise>
          </choose>
    </select>
                                                                             
<!-- 获取角色拥有的菜单ID集合 -->
    <select id="listMenuIdsByRoleId" resultType="java.lang.Long">
        SELECT
            rm.menuId
        FROM
            sys_role_menu rm
                INNER JOIN sys_menu m ON rm.menuId = m.id
        WHERE
            rm.roleId = #{roleId}
    </select>
                                                                             
    <!-- 获取用户导出列表 -->
    <select id="listExportUsers" resultType="com.youlai.system.model.vo.UserExportVO">
        SELECT
            u.username,
            u.nickname,
            u.mobile,
            CASE u.gender
                WHEN 1 THEN '男'
                WHEN 2 THEN '女'
                ELSE '未知'
            END gender,
            d.NAME AS dept_name,
            u.createTime
        FROM
            sys_user u
                LEFT JOIN sys_dept d ON u.deptId = d.id
        <where>
            u.isDelete = 0 AND u.username != 'root'
            <if test='keywords!=null and keywords.trim() neq ""'>
                AND (u.username LIKE CONCAT('%',#{keywords},'%')
                OR u.nickname LIKE CONCAT('%',#{keywords},'%')
                OR u.mobile LIKE CONCAT('%',#{keywords},'%'))
            </if>
            <if test='status!=null'>
                AND u.status = #{status}
            </if>
            <if test='deptId!=null'>
                AND concat(',',concat(d.tree_path,',',d.id),',') like concat('%,',#{deptId},',%')
            </if>
        </where>
        GROUP BY u.id
    </select>