分页查询

 select * from (
             SELECT
      __T1.ORDER_SN as ORDER_SN,
      __T1.RES_ORDER_SN as RES_ORDER_SN,
      __T1.PROJECT_NAME as PROJECT_NAME,
      __T1.SX_UNITNAME as SX_UNITNAME,
      __T1.MARGIN_AMOUNT as MARGIN_AMOUNT,
      __T1.SERVICE_AMOUNT as SERVICE_AMOUNT,
      __T1.PHONE as PHONE,
      __T1.CREDIT_CODE_ZBR as CREDIT_CODE_ZBR,
      __T1.CREDIT_CODE_TBR as CREDIT_CODE_TBR,
      __T1.STATUS as STATUS,
      __T1.GURANTEE_TYPE as GURANTEE_TYPE,
      __T1.DESCRIPTION as DESCRIPTION,
      __T1.ISP_CODE as ISP_CODE,
      __T1.CREATE_TIME as CREATE_TIME,
      __T1.MODIFY_TIME as MODIFY_TIME,
      __T1.BANK_NAME as BANK_NAME,
      __T1.ACCOUNT_NAME as ACCOUNT_NAME,
      __T1.BANK_CARDNUM as BANK_CARDNUM,
      isp.ISP_NAME AS ISP_NAME,
     row_number() 
     over(order by __T1.MODIFY_TIME DESC) n 
     from T_TRADE as __T1
     LEFT JOIN T_ISP isp ON __T1.ISP_CODE = isp.ISP_CODE
 WHERE 1=1 {{__where}}
     )hhh 
     where hhh.n>(#{__page_index} * #{__page_size})-#{__page_size} and hhh.n<=#{__page_index} * #{__page_size}

 

  DECLARE @pageCount INT DECLARE @pageNo INT
SET
  @pageCount = #{__page_size}
SET
  @pageNo = #{__page_index}
select
  tbPage.*
from
  (
    select
      tbTemp.*,
      row_number() over(
        ORDER BY
BEIANDIQU_CODE ASC,REGION_CODE asc,SERIAL_NUMBER asc
      ) as rownum__
    from
      (
select
      __T1.ID as ID,
      __T1.BEIANDIQU_CODE as BEIANDIQU_CODE,
      a1.label as BEIANDIQU_NAME,
      __T1.REGION_CODE as REGION_CODE,
      __T1.ISP_GUID as ISP_GUID,
      a0.label as ISP_GUID__label,
      a2.AREANAME as REGION_CODE_NAME,
      __T1.SERIAL_NUMBER as SERIAL_NUMBER,
      __T1.CREATE_TIME as CREATE_TIME,
      __T1.MODIFY_TIME as MODIFY_TIME
    from
      T_AREACODE_ISP_SORT __T1
      left join (
        SELECT
          ISP_NAME AS label,
          DATAID AS value
        FROM
          T_ISP
        where
          IS_ENABLE = 1
      ) a0 on __T1.ISP_GUID = a0.value
       left join (
       SELECT '自主招标' as label,'x00004' AS VALUE  
UNION SELECT '公e采' as label,'x30001' AS Value
UNION SELECT '火炬集团' as label,'x30002' AS Value
UNION SELECT '国资' as label,'x60004' AS Value
UNION SELECT '福清' as label,'x60005' AS Value
UNION SELECT '尤溪数智招标采购平台' as label,'x60006' AS Value
UNION SELECT '将乐数智招标采购平台' as label,'x60007' AS Value
UNION SELECT '斗门' as label,'x80001' AS Value
UNION SELECT '福建中烟' as label,'x90009' AS Value
UNION SELECT '小规模工程' as label,'x70001' AS Value
UNION SELECT '小规模工程' as label,'x70001' AS Value
UNION SELECT '龙岩' as label,'x60010' AS Value
      ) a1 on __T1.BEIANDIQU_CODE = a1.value
left join SYS_AREA a2 on a2.AREACODE = __T1.REGION_CODE
  ) tbTemp
where 1= 1
<if test="BEIANDIQU_CODE != null and BEIANDIQU_CODE != '' and BEIANDIQU_CODE=='000000' " >
            <![CDATA[
            AND tbTemp.BEIANDIQU_CODE NOT LIKE 'x%'
        ]]>
        </if>

        <if test="BEIANDIQU_CODE != null and BEIANDIQU_CODE != '' and BEIANDIQU_CODE !='000000'" >
            <![CDATA[
             AND tbTemp.BEIANDIQU_CODE = #{BEIANDIQU_CODE}
        ]]>
        </if>
        
         <if test="REGION_CODE != null and REGION_CODE != '' and REGION_CODE !='000000'" >
            <![CDATA[
             AND tbTemp.REGION_CODE = #{REGION_CODE}
        ]]>
        </if>
) tbPage
where
rownum__ BETWEEN (@pageNo - 1) * @pageCount + 1
  AND @pageCount * @pageNo