如上查询结果,通过主表左连接出来多条记录。
目前的查询结果共有12条记录,但是根据 id来看,实际上只有2条主表记录,其它的都是
通过主表左连接出来的。
如何让查询结果只有2条?
MyBatis提供了合并的语法
@Data
public class Subsystem {
private int subsystemId;
private String subsystemKey;
private String subsystemName;
private Integer sn;
private boolean selected;
//持有Page对象集合
private List<Page> pages;
}
@Data
public class Page {
private int pageId;
private String pageKey;
private String pageName;
private boolean selected;
private Integer parentPageId;
private Integer sn;
private List<Page> subPages;
}
<select id="list" resultMap="listWithPage">
SELECT
ts.id,
ts.subsystem_key,
ts.subsystem_name,
tp.id as page_id,
tp.page_key,
tp.page_name,
ts.SN,
tp.SN as page_sn,
tp.parent_page_id
FROM t_subsystem ts
LEFT JOIN t_page tp ON ts.id = tp.subsystem_id
ORDER BY ts.SN , tp.SN
</select>
<resultMap id="listWithPage"
<!-- 这里返回最终的对象类型-->
type="${package}.Subsystem">
<id property="subsystemId" column="id"/>
<result property="subsystemName" column="subsystem_name"/>
<result property="subsystemKey" column="subsystem_key"/>
<result property="sn" column="sn"/>
<collection property="pages"
<!-- 这里填写持有的集合对象类型-->
ofType="${package}.Page">
<id property="pageId" column="page_id"/>
<result property="pageKey" column="page_key"/>
<result property="pageName" column="page_name"/>
<result property="sn" column="page_sn"/>
<result property="parentPageId" column="parent_page_id"/>
</collection>
</resultMap>