mybatis 一对多分页查询数据条数不匹配解决

在项目中，使用mybatis做一对多关联，出现数据条数不匹配的情况，比如要查询10条，由于一对多的关系导致最终得到的数据条数变少。

外键abum_id关联tp_abum表的主键，表结构如下：

实体：

xml：

  保存后的数据如下：

sql：SELECT

abum.*,
        summary.id AS summary_id,
        summary.summary,
        summary.type AS summary_type,
        summary.compositor
        from
        `tp_abum` abum
        left join
        `tp_abum_summary` summary
        on
        abum.id = summary.abum_id

where   `status` <> '0' and create_member_code ='1' ORDER BY create_time desc  limit 0,10;

获得数据：

  很明显，mybatis一对多处理后，TPAbumAO的记录数少于10条。

  原因是tp_abum和tp_abum_summary关联查询时，由于重复数据导致分页取到tp_abum表的数据不完整，解决方法是外面不用limit,在where条件中，对abum_id使用in子查询，在in子查询里面用limit，取出指定limit的满足条件还没有外连接的评论的id.

sql：SELECT
abum.*,
        summary.id AS summary_id,
        summary.summary,
        summary.type AS summary_type,
        summary.compositor
        from
        `tp_abum` abum
        left join
        `tp_abum_summary` summary
        on
        abum.id = summary.abum_id
where  abum.id in (SELECT TEMP.id from (SELECT id from `tp_abum` where `status` <> '0' and create_member_code ='1' ORDER BY create_time desc  limit 0,10) as temp) ORDER BY abum.create_time desc ;

  最后查询结果即是正确的分页结果：

  相关文章网址：点击打开链接

下面是我碰到问题的代码......

<!--分页查询 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX -->
	<select id="selectCompanyAmbientInfo" resultMap="companyAndAmbient">
		select
		a.*,b.province,b.city,b.county,c.*
		from
		t_company a
		left join
		t_company_address b on
		a.count_id =
		b.aid
		left
		join
		php_ambient c on
		a.name = c.psname and
		c.updatetime =
		(select
		max(updatetime) from
		php_ambient)
		where a.id in 
		(select  x.id from t_company x
		left join php_ambient y on x.name=y.psname and
		y.updatetime =
		(select
		max(z.updatetime) from
		php_ambient z)
		<where>
			x.delete is null
			<if test="info.en_name!=null and info.en_name!=''">and x.name like CONCAT('%',#{info.en_name},'%' )</if>
			<if test="info.countId!=null and info.countId!=''">and x.count_id = #{info.countId}</if>
			<if test="info.followLevel!=null and info.followLevel!=''">and x.follow_level = #{info.followLevel}</if>
			<if test="info.isonlineMonitor!=null and info.isonlineMonitor!=''">and x.isonline_monitor = #{info.isonlineMonitor}</if>
			<if test="info.trade!=null and info.trade!=''">and x.trade = #{info.trade}</if>
			<if test="code=='so2,nox,pm'">
				and y.isso2 = 1 and y.isnox = 1 and c.ispm = 1
			</if>
			<if test="code=='so2,nox'">
				and y.isso2 = 1 and y.isnox = 1
			</if>
			<if test="code=='nox,pm'">
				and y.isnox = 1 and y.ispm = 1
			</if>
			<if test="code=='so2'">
				and y.isso2 = 1
			</if>
			<if test="code=='nox'">
				and y.isnox = 1 
			</if>
			<if test="code=='pm'">
				and y.ispm = 1
			</if>
		</where>
		GROUP BY(x.id)
		ORDER BY x.isonline_monitor desc
		limit #{pageSize} offset #{pageNum})
	</select>