使用inner join查询同一张表，将一张表中的父菜单和子菜单

• mybatis代码

<resultMap id="menuMap" type="com.github.powertest.pojo.Menu">
        <id column="pmenu_id" property="menuId"/>
        <result column="pparent_id" property="parentId"/>
        <result column="pmenu_name" property="menuName"/>
        <result column="pmenu_url" property="menuUrl"/>
        <collection property="sonMenu" ofType="com.github.powertest.pojo.Menu">
            <id column="smenu_id" property="menuId"/>
            <result column="sparent_id" property="parentId"/>
            <result column="smenu_name" property="menuName"/>
            <result column="smenu_url" property="menuUrl"/>
        </collection>
    </resultMap>


    <select id="listMenu" resultMap="menuMap">
        select m.menu_id as pmenu_id ,m.parent_id as pparent_id
        ,m.menu_name as pmenu_name,m.menu_url as pmenu_url
        ,mm.menu_id as smenu_id, mm.parent_id as sparent_id ,mm.menu_name as smenu_name,mm.menu_url as smenu_url
        from menu m
        left join menu mm on m.menu_id=mm.parent_id
        where m.parent_id=0
    </select>

• 执行sql语句如下

  
  image.png

• java的model层

public class Menu {
    private Long menuId;

    private Long parentId;

    private String menuName;

    private String menuUrl;

    private String status;

    private List<Menu> sonMenu;

}