分页查询

复制代码
 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
复制代码

 

posted on   五官一体即忢  阅读(7)  评论(0编辑  收藏  举报

相关博文:
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
· DeepSeek 开源周回顾「GitHub 热点速览」
< 2025年3月 >
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5

导航

统计

点击右上角即可分享
微信分享提示