SQL分页排序的实现与分页数据重复问题——以Oracle rownum为例

SQL分页排序的实现与分页数据重复问题——以Oracle rownum为例

对于关系数据库来说，直接写SQL拉数据在列表中显示是很常用的做法。但如此便带来一个问题：当数据量大到一定程度时，系统内存迟早会耗光。另外，网络传输也是问题。如果有1000万条数据，用户想看最后一条，这时即便有足够的内存，在网络上传输这么多数据也得一两小时吧，恐怕没几个用户有这么耐心等。因此分页是必须的。

现在网上的论坛、博客什么的，基本上都会有分页功能，有些是SQL分页的，有些可能是NOSQL用其它方法分页，都有很成熟的东西了。本文根据我自己的经验，以Oracle为例，讲下简单的SQL分页和排序问题，对刚接触SQL准备要做分页的人有些帮助吧，大牛们就不必看了。

假设ORALCE数据库中有一个TAB001表，主键为ID，有1000万条记录，索引什么的都有了。我们有一个需求，是在界面上列出指定条件的记录，原始SQL如下：

select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS from TAB001 where ATYPE='SOME_TYPE'

如果要排序，比如要按CREATOR倒排序，我们会在SQL后面再加一句：order by CREATEOR desc

现在，我们发现这个SQL下来有500万条记录，显然，如果不分页，系统很容易就会翘掉。于是我们准备分页。

分页前，我们可能要在界面上摆上几个按钮和状态显示：上一页、下一页、第一页、最后页、每页X条、共M页、当前第N页、跳到第N页，等。显然，我们分页的步骤如下：

计算总记录数；
根据总记录数和每页记录数，计算总页数；
根据当前要显示的页码，计算起始和结束的记录号；
生成分页SQL，执行之，返回本页数据，显示之。
首先，计算总记录数。这个简单，嵌套一个select count(*)就行了：

select count(*)
  from (
             select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS from TAB001 where ATYPE='SOME_TYPE'
          ) xx
 
然后，总页数=ceil(总记录数/每页记录数)，不足一页也当一页处理。

接着，假设现在是第N页，则本页的开始、结束记录号为：

  开始记录号=N*每页记录数

  结束记录号=min((N+1)*每页记录数-1,总记录数)

最后，生成分页SQL。由于分页需要有记录号，因此先要嵌套一个子查询生成ROWNUM：

select rownum as recordno
  from (
             select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS from TAB001 where ATYPE='SOME_TYPE'
          ) xx
 
这样，我们就有了记录号，可以再对记录号进行过滤，只选出本页开始记录号之后、结束记录号之前的记录：

select xxx.*
  from (
            select rownum as recordno
              from (
                         select ID,NAME,ATYPE,CREATEDATE,CREATOR,ASTATUS from TAB001 where ATYPE='SOME_TYPE'
                      ) xx
           ) xxx
where recordno >= :开始记录号
    and recordno <= :结束记录号