分页查询一对多,查询结果少于分页规定条数

今天在写一个分页查询的时候发现,查询Vo对象应该有10条结果,Vo中有一个List字段,list中的对象和Vo对象是多对一的关系,导致sql语句查出10条,其中有多条是多的一方被放入list中,结果导致Vo条数减少

如图

SELECT
	a.`id` AS id,
	a.`report_user_id` AS reportUserId,
	a.`report_no` AS reportNo,
	a.`report_recoded` AS reportRecoded,
	a.`report_content` AS reportContent,
	a.`report_service_id` AS reportServiceId,
	a.`report_service_name` AS reportServiceName,
	a.`report_assessment_average_price` AS reportAssessmentAveragePrice,
	a.`report_assessment_total_price` AS reportAssessmentTotalPrice,
	a.`report_assessment_float_price` AS reportAssessmentFloatPrice,
	a.`report_assessment_float_rate` AS reportAssessmentFloatRate,
	a.`report_apartment_layout` AS reportApartmentLayout,
	a.`report_orientation` AS reportOrientation,
	a.`report_floor` AS reportFloor,
	a.`report_total_floor` AS reportTotalFloor,
	a.`report_buliding_no` AS reportBulidingNo,
	a.`report_address` AS reportAddress,
	a.`report_room` AS reportRoom,
	a.`report_area` AS reportArea,
	a.`operate_date` AS operateDate,
	a.`report_year` AS reportYear,
	a.`basic_price` AS basicPrice,
	b.datasource_name as datasourceName,
	c.`service_name` AS `serviceName`,
	g.region_name AS cityName,
	e.shop_nickName AS shopNickName,
	f.username AS operationUser
FROM
	ny_realestate_report a
INNER JOIN ny_realestate_report_datasource b ON a.`id` = b.`report_id`
INNER JOIN ny_service c ON a.`report_service_id` = c.`id`
INNER JOIN ny_algorithm d ON c.algorithm_id = d.id
INNER JOIN ny_shop e ON c.shop_id = e.id
INNER JOIN ny_user f ON a.report_user_id = f.id
INNER JOIN ny_region g ON d.algorithm_city = g.region_id
ORDER BY
	a.operate_date DESC
LIMIT 3,10;

关联关系resultMap

<resultMap type="com.nuoya.zzx.extend.vo.NyRealestateReportVo" id="voMap">
		<id column="id" jdbcType="INTEGER" property="id" />
		<result column="report_user_id" jdbcType="INTEGER" property="reportUserId" />
		<result column="report_no" jdbcType="VARCHAR" property="reportNo" />
		<result column="report_recoded" jdbcType="VARCHAR" property="reportRecoded" />
		<result column="report_content" jdbcType="VARCHAR" property="reportContent" />
		<result column="report_service_id" jdbcType="INTEGER" property="reportServiceId" />
		<result column="report_service_name" jdbcType="VARCHAR" property="reportServiceName" />
		<result column="report_assessment_average_price" jdbcType="DECIMAL" property="reportAssessmentAveragePrice" />
		<result column="report_assessment_total_price" jdbcType="DECIMAL" property="reportAssessmentTotalPrice" />
		<result column="report_assessment_float_price" jdbcType="DECIMAL" property="reportAssessmentFloatPrice" />
		<result column="report_assessment_float_rate" jdbcType="DECIMAL" property="reportAssessmentFloatRate" />
		<result column="report_apartment_layout" jdbcType="VARCHAR" property="reportApartmentLayout" />
		<result column="report_orientation" jdbcType="VARCHAR" property="reportOrientation" />
		<result column="report_floor" jdbcType="TINYINT" property="reportFloor" />
		<result column="report_total_floor" jdbcType="TINYINT" property="reportTotalFloor" />
		<result column="report_buliding_no" jdbcType="VARCHAR" property="reportBulidingNo" />
		<result column="report_address" jdbcType="VARCHAR" property="reportAddress" />
		<result column="report_room" jdbcType="VARCHAR" property="reportRoom" />
		<result column="report_area" jdbcType="DECIMAL" property="reportArea" />
		<result column="report_datasource_id" jdbcType="INTEGER" property="reportDatasourceId" />
		<result column="report_datasource_name" jdbcType="VARCHAR" property="reportDatasourceName" />
		<result column="report_assessment" jdbcType="DECIMAL" property="reportAssessment" />
		<result column="report_year" jdbcType="VARCHAR" property="reportYear" />
		<result column="operate_date" jdbcType="TIMESTAMP" property="operateDate" />
		<result column="basic_price" jdbcType="DECIMAL" property="basicPrice" />
		<result column="city_name" jdbcType="DECIMAL" property="cityName" />
		<result column="shop_nickName" jdbcType="DECIMAL" property="shopNickName" />
		<result column="username" jdbcType="DECIMAL" property="operationUser" />
		<result column="service_name" jdbcType="VARCHAR" property="serviceName" />
		<result column="report_type" jdbcType="INTEGER" property="reportType" />
		<result column="report_apply_id" jdbcType="INTEGER" property="reportApplyId" />
		<result column="operation_status" jdbcType="INTEGER" property="operationStatus" />
		<result column="apply_date" jdbcType="TIMESTAMP" property="applyDate" />
		<result column="remark" jdbcType="VARCHAR" property="remark" />
		<result column="proprietary_certificateUrl" jdbcType="VARCHAR" property="proprietaryCertificateUrl" />

		<!-- 一对多的关系 -->
		<!-- property: 指的是集合属性的值, ofType：指的是集合中元素的类型 -->
		<collection property="list" ofType="com.nuoya.zzx.base.po.NyRealestateReportDatasource" >
			<id column="b_id" jdbcType="INTEGER" property="id" />
			<result column="report_id" jdbcType="INTEGER" property="reportId" />
			<result column="datasource_id" jdbcType="INTEGER" property="datasourceId" />
			<result column="datasource_name" jdbcType="VARCHAR" property="datasourceName" />
			<result column="assessment_value" jdbcType="DECIMAL" property="assessmentValue" />
			<result column="publis_date" jdbcType="TIMESTAMP" property="publisDate" />
			<result column="b_operate_date" jdbcType="TIMESTAMP" property="operateDate" />
		</collection>
	</resultMap>

本来应该查询10条数据,其中三条是一对多中多的一方,放入list生成一个Vo,结果最终数据减少

可以使用GROUP_CONCAT加上group by解决

SELECT
	a.`id` AS id,
	a.`report_user_id` AS reportUserId,
	a.`report_no` AS reportNo,
	a.`report_recoded` AS reportRecoded,
	a.`report_content` AS reportContent,
	a.`report_service_id` AS reportServiceId,
	a.`report_service_name` AS reportServiceName,
	a.`report_assessment_average_price` AS reportAssessmentAveragePrice,
	a.`report_assessment_total_price` AS reportAssessmentTotalPrice,
	a.`report_assessment_float_price` AS reportAssessmentFloatPrice,
	a.`report_assessment_float_rate` AS reportAssessmentFloatRate,
	a.`report_apartment_layout` AS reportApartmentLayout,
	a.`report_orientation` AS reportOrientation,
	a.`report_floor` AS reportFloor,
	a.`report_total_floor` AS reportTotalFloor,
	a.`report_buliding_no` AS reportBulidingNo,
	a.`report_address` AS reportAddress,
	a.`report_room` AS reportRoom,
	a.`report_area` AS reportArea,
	a.`operate_date` AS operateDate,
	a.`report_year` AS reportYear,
	a.`basic_price` AS basicPrice,
	GROUP_CONCAT(b.`datasource_name`) AS datasourceName,
	c.`service_name` AS `serviceName`,
	g.region_name AS cityName,
	e.shop_nickName AS shopNickName,
	f.username AS operationUser
FROM
	ny_realestate_report a
INNER JOIN ny_realestate_report_datasource b ON a.`id` = b.`report_id`
INNER JOIN ny_service c ON a.`report_service_id` = c.`id`
INNER JOIN ny_algorithm d ON c.algorithm_id = d.id
INNER JOIN ny_shop e ON c.shop_id = e.id
INNER JOIN ny_user f ON a.report_user_id = f.id
INNER JOIN ny_region g ON d.algorithm_city = g.region_id
group by a.id
ORDER BY
	a.operate_date DESC
LIMIT 0,10;

这样返回结果就不会减少了