mybatis分页 sql 语句

 

        分类：            sql 2013-04-28 15:54 10人阅读 评论(0) 收藏 举报

/*自己新增的view*/

<resultMap id="depositItemMap" type="com.suning.auction.model.DepositListExample">
<result property="itemName" column="ITEM_NAME" jdbcType="VARCHAR"/>
<result property="custId" column="CUST_ID" jdbcType="BIGINT"/>
<result property="depStatus" column="DEP_STATUS" jdbcType="CHAR"/>
<result property="itemId" column="ITEM_ID" jdbcType="BIGINT" />
<result property="depositAmount" column="DEPOSIT_AMOUNT" />
<result property="lockTime" column="LOCK_TIME" jdbcType="TIMESTAMP"/>
<result property="unlockTime" column="UNLOCK_TIME" jdbcType="TIMESTAMP"/>
<result property="submitTime" column="SUBMIT_TIME" jdbcType="TIMESTAMP"/>
<result property="mainImgGroupId" column="MAIN_IMG_GROUP_ID" jdbcType="BIGINT"/>
< /resultMap>

/*mybatis分页 sql 语句*/

<select id="selectDepositItemPage" resultMap="depositItemMap" parameterType="map" >

  select * from (
select ROW_NUMBER() OVER(ORDER BY EPAY_ID DESC) AS ROWNUM,
    b.item_name
    ,b.MAIN_IMG_GROUP_ID
    ,d.CUST_ID,d.DEP_STATUS,d.ITEM_ID,d.DEPOSIT_AMOUNT,d.LOCK_TIME,d.UNLOCK_TIME,d.SUBMIT_TIME
FROM
    AUCT_ITEM b  ,AUCT_deposit d
    where 
d.item_id=b.item_id
  <if test="depStatus != null" >
       and d.DEP_STATUS = #{depStatus,jdbcType=CHAR}
  </if>
  <if test="custId != null" >
       and d.CUST_ID = #{custId,jdbcType=BIGINT}
  </if>
   ) c
      where 1=1
<![CDATA[ and ROWNUM >= #{startNum} and ROWNUM <= #{endNum} ]]>
  </select>