如果直接 主表 join 子表 limit paizeSize,可能造成最后一条主表数据对应的子表信息不全,被limit截掉了。
所以应该根据业务主表来分页,再关联字表,保证返回的正确行数并且主表信息完整
<resultMap id="ResultOrderMap" type="com.XXX.HEADERDTO">
<result column="CPS_ORDER_NO" property="CPS_ORDER_NO"/>
<result column="SALES_DEPARTMENT" property="SALES_DEPARTMENT"/>
<result column="STOREID" property="BILLING_STOREID"/>
<result column="STOREID" property="STOREID"/>
<result column="STORENAME" property="STORENAME"/>
<result column="FULLCARTONQTY" property="FULLCARTONQTY" javaType="BigDecimal" jdbcType="VARCHAR"/>
<result column="STOREADDRESS_NO" property="STOREADDRESS_NO"/>
<result column="STOREADDRESS" property="STOREADDRESS"/>
<result column="ORDER_TIME" property="ORDER_TIME"/>
<result column="NOTES1" property="NOTES"/>
<collection property="detailsItem" ofType="com.xxxx.RowsDTO">
<result column="id" property="id"/>
<result column="CPS_ORDER_NO" property="BL_ORDER_NO"/>
<result column="SKU" property="SKU"/>
<result column="SKU_NAMEC" property="SKU_NAMEC"/>
<result column="UOM" property="UOM"/>
<result column="QTY_PACK" property="QTY_PACK" javaType="INTEGER" jdbcType="VARCHAR"/>
<result column="QTY_ORDERED_EACH" property="QTY_ORDERED_EACH" javaType="BigDecimal" jdbcType="VARCHAR"/>
<result column="QTY_ORDERED" property="QTY_ORDERED" javaType="BigDecimal" jdbcType="VARCHAR"/>
<result column="NOTES2" property="NOTES"/>
</collection>
</resultMap>
<!--先limit出主表,明细表inner join主表查询,解决mybatis一对多转换+limit限制时,主表最后一条信息不全问题,导致同一订单分两部分发送到tms-->
<select id="getTmsOrderList" resultMap="ResultOrderMap">
SELECT
he.transfer_no AS CPS_ORDER_NO,
he.sap_group AS SALES_DEPARTMENT,
he.sap_code AS STOREID,
he.shop_name AS STORENAME,
he.goods_count AS FULLCARTONQTY,
he.created AS ORDER_TIME,
he.remark AS NOTES1,
ad.id AS STOREADDRESS_NO,
ad.address AS STOREADDRESS,
dtl.rec_id AS id,
dtl.mdm_product_code AS SKU,
dtl.mdm_product_name AS SKU_NAMEC,
dtl.unit_name AS UOM,
dtl.mdm_total AS QTY_PACK,
CONVERT(dtl.mdm_total * dtl.num,DECIMAL (14, 3)) AS QTY_ORDERED_EACH,
dtl.num AS QTY_ORDERED,
dtl.remark AS NOTES2
FROM jxb_transfer_stock_transfer_detail dtl
INNER JOIN (
select id,transfer_id,transfer_no,shop_name,to_warehouse_address,
sap_group,sap_code,goods_count,remark,created
from jxb_transfer_stock_transfer
where status='90' AND send_flag in (0,2,3)
ORDER BY modified LIMIT #{pageSize}
) he ON dtl.transfer_id = he.transfer_id
LEFT JOIN jxb_transfer_tms_address ad ON ad.id=he.to_warehouse_address
WHERE ad.id is not null
</select>