【笔记】使用临时表，处理菜单

<resultMap type="AuthMenu" id="AuthMenuMapper">
        <id property="id" column="id" />
        <collection property="childList" ofType="AuthMenu"
                    select="selectChildListByParentId"
                    column="id"/>
    </resultMap>

    <!-- 根据角色id 关联查询菜单及其所有子孙菜单 -->
    <select id="selectMenuListByRoleIdAndMenuId" resultMap="AuthMenuMapper">
        <!-- 1.创建临时表, 将该用户角色可以访问的菜单放入临时表, 为 在可访问菜单的基础上作自关联 准备数据 -->
        create temporary table  temp_auth_menu 
        SELECT am.* FROM auth_role_menu arm 
        INNER JOIN auth_menu am ON arm.menu_id = am.id 
        WHERE arm.role_id = #{roleId} AND am.`status` = 1;
        <!-- 2.从临时表取数据, 开始自关联 -->
        SELECT * FROM temp_auth_menu where id = #{menuId}
    </select>

    <!-- 根据父id 递归查询指定菜单及其所有子孙菜单 -->
    <select id="selectChildListByParentId" resultMap="AuthMenuMapper">
        select * from temp_auth_menu
        where parent_id = #{id} 
        order by sort_num asc
    </select>