mybatis3.2.1版本 ResultMap匹配遇到的一个问题 乱序匹配不成功

今天在做一个简单开发的时候遇到了一个很奇怪的问题。

    <resultMap id="BaseResultMapWithOrderInfo" type="com.abc.entry.vo.ShelfLayerVo">
        <id column="sl_id" property="slId"/>
        <result column="sl_shelf_no" jdbcType="VARCHAR" property="slShelfNo"/>
        <result column="sl_label" jdbcType="VARCHAR" property="slLabel"/>
        <result column="sl_remark" jdbcType="VARCHAR" property="slRemark"/>
        <collection property="shelfGoodsList" ofType="com.abc.entry.vo.ShelfGoodsWithOrderInfoVo">
            <id column="sg_id" property="sgId"/>
            <result column="sg_name" property="sgName"/>
            <result column="sg_price" property="sgPrice"/>
            <result column="slg_quantity" property="slgQuantity"/>
            <result column="sg_barcode" property="sgBarcode"/>
            <result column="sg_img_url" property="sgImgUrl"/>
            <result column="sg_remark" property="sgRemark"/>
            <result column="paying_amt" property="payingAmt"/>
            <result column="paid_amt" property="paidAmt"/>
            <result column="completed_amt" property="completedAmt"/>
            <result column="canceled_amt" property="canceledAmt"/>
            <result column="total_quantity" property="totalQuantity"/>
        </collection>
    </resultMap>

<select id="getShelfLayerVoWithOrder" resultMap="BaseResultMapWithOrderInfo">
        select
         sl.sl_id,sl.sl_shelf_no,sl.sl_label,
         sl.sl_remark,sg.sg_id,sg.sg_name,slg.slg_price as sg_price, slg.slg_quantity as slg_quantity,
         sg.sg_barcode,sg.sg_img_url,sg.sg_remark, ifnull(tmp.paying_amt, 0) as paying_amt, ifnull(tmp.paid_amt, 0) as paid_amt,
         ifnull(tmp.completed_amt, 0) as completed_amt, ifnull(tmp.canceled_amt, 0) as canceled_amt,
         (slg.slg_quantity + ifnull(tmp.paying_amt, 0) + ifnull(tmp.paid_amt, 0)  + ifnull(tmp.completed_amt, 0)) as total_quantity
      from shelf_layer sl
          left join shelf_layer_goods slg on sl.sl_id=slg.slg_layer_id
          left join shelf_goods sg on slg.slg_goods_id=sg.sg_id
          left join (
          select sod.sod_slg_id,  sod.sod_goods_name,
            sum( case so.so_status when 0 then sod.sod_goods_amt  end )  paying_amt,
            sum( case so.so_status when 1 then sod.sod_goods_amt  end )  paid_amt,
            sum( case so.so_status when 2 then sod.sod_goods_amt  end )  completed_amt,
            sum( case so.so_status when 3 then sod.sod_goods_amt  end )  canceled_amt
          from shelf_order so
            left join shelf_order_detail sod on so.so_no = sod.sod_order_no
          where so.so_shelf_no = #{seNo} and
              so.so_create_time >= #{startDate}
        <if test="endDate !=null and endDate !=''">
            and so.so_create_time &lt; #{endDate}
        </if>
              group by sod.sod_slg_id
          ) tmp  on slg.slg_id = tmp.sod_slg_id
      where sl.sl_shelf_no= #{seNo}
        order by sl.sl_id asc
    </select>

请注意上面select语句最后面的order by语句。

在未使用排序时，查询结果顺序是错乱的。导致sl_id为1的数据，占据了sl_id为2的数据的映射位置。

 

强烈建议：在使用这种映射方法时，要按照映射id排序

转载于:https://www.cnblogs.com/zixiaoguan/p/8658953.html