你好,游客 登录 注册 搜索
背景:
阅读新闻

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

[日期:2011-02-22] 来源:Linux社区  作者:huzgd [字体: ]

对于关系数据库来说,直接写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 <= :结束记录号

linux
相关资讯       Oracle教程 
本文评论   查看全部评论 (1)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款
第 1 楼
* 匿名 发表于 2013/11/6 16:43:34
我们也出现了这个问题