分页语句

最新推荐文章于 2023-12-15 14:28:28 发布

原创最新推荐文章于 2023-12-15 14:28:28 发布 · 387 阅读

1 ·

CC 4.0 BY-SA版权

Orale MySQL 专栏收录该内容

73 篇文章

订阅专栏

本文介绍在Oracle和Sybase数据库中实现分页查询的方法。Oracle示例展示了如何使用ROWNUM进行记录排序并限制返回结果的数量；Sybase示例则使用RANK()窗口函数来实现类似效果。这些技术对于提高大型数据集的查询效率至关重要。

Oracle分页：


SELECT *
  FROM (SELECT T.*, ROWNUM R
          FROM (SELECT C.SCENIC_NAME POSITION_NAME,
                       B.PROVICE PRO_NAME,
                       SUM(A.TOURISTSNUM) VISITORS
                  FROM KEYUANFENXI A, CITY_MANAGER B, AREA_MANAGER C
                 WHERE A.ACQUISITIONTIME >= TO_CHAR(SYSDATE - 7, 'YYYYMMDD')
                   AND A.ACQUISITIONTIME < TO_CHAR(SYSDATE, 'YYYYMMDD')
                   AND A.SCENICID = #{positionId}
                   AND A.SCENICID = C.SCENICID
                   AND B.CITYID = A.TRAVELERSSOURCE
                   AND C.TYPE IN (1, 2, 3, 4)
                 GROUP BY C.SCENIC_NAME, B.PROVICE
                 ORDER BY VISITORS DESC) T
         WHERE ROWNUM <= (PAGE * PAGESIZE))
 WHERE R >= (PAGE - 1) * PAGESIZE

Sybase分页：


SELECT *
  FROM (SELECT RANK() OVER(ORDER BY W.WEB_ID) NUM, W.WEB_ID, W.WEB_NAME
          FROM RESORT_CFG_WEB W
         WHERE 1 = 1
           AND W.WEB_NAME LIKE '%' || #{WEB_NAME} || '%'
           AND W.WEB_ID = #{WEB_ID}) T
WHERE T.NUM > (#{PAGE} - 1) * #{PAGESIZE}
 AND T.NUM <= (#{PAGE} * #{PAGESIZE})