文档章节

数据库查询返回特定结果即分页查询

zimingforever
 zimingforever
发布于 2013/06/08 23:58
字数 2536
阅读 4131
收藏 24

1 几种不同数据库的不同的分页写法:

a mysql

a) 查询前n条记录
select * from table_name limit 0,n
b) 查询第n条到第m条
select * from table_name limit n,m

b oracle 

a)查询前n条记录
select * from table_name where rownum
b)查询第m条到第n条记录:
select * from (select a.*,a.rownum rn from table_name where rownum<n) where rn>m
c sqlserver
a)查询前n条记录:
select top n * from table_name;
b)查询第n条到第m条记录:
select top n * from (select top m * from table_name order by column_name) a order by column_name desc

2 oracle rownum的用法

对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号,返回的第一行分配的是1,第二行是2,依此类推,这个伪字段可以用于限制查询返回的总行数,而且rownum不能以任何表的名称作为前缀。

(1) rownum 对于等于某值的查询条件
如果希望找到学生表中第一条学生的信息,可以使用rownum=1作为条件。但是想找到学生表中第二条学生的信息,使用rownum=2结果查不到数据。因为rownum都是从1开始,但是1以上的自然数在rownum做等于判断是时认为都是false条件,所以无法查到rownum = n(n>1的自然数)。
SQL> select rownum,id,name from student where rownum=1;(可以用在限制返回记录条数的地方,保证不出错,如:隐式游标)

(2)rownum对于大于某值的查询条件
   如果想找到从第二行记录以后的记录,当使用rownum>2是查不出记录的,原因是由于rownum是一个总是从1开始的伪列,Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立,所以查不到记录。

    那如何才能找到第二行以后的记录呀。可以使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名,否则还是不会查出记录来,这是因为rownum不是某个表的列,如果不起别名的话,无法知道rownum是子查询的列还是主查询的列。
SQL>select * from(select rownum no ,id,name from student) where no>2;

(3)rownum对于小于某值的查询条件
    如果想找到第三条记录以前的记录,当使用rownum<3是能得到两条记录的。显然rownum对于rownum<n((n>1的自然数)的条件认为是成立的,所以可以找到记录。
SQL> select rownum,id,name from student where rownum <3;

    综上几种情况,可能有时候需要查询rownum在某区间的数据,那怎么办呀从上可以看出rownum对小于某值的查询条件是人为true的,rownum对于大于某值的查询条件直接认为是false的,但是可以间接的让它转为认为是true的。那就必须使用子查询。例如要查询rownum在第二行到第三行之间的数据,包括第二行和第三行数据,那么我们只能写以下语句,先让它返回小于等于三的记录行,然后在主查询中判断新的rownum的别名列大于等于二的记录行。但是这样的操作会在大数据集中影响速度。
SQL> select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;

(4)rownum和排序
    Oracle中的rownum的是在取数据的时候产生的序号,所以想对指定排序的数据去指定的rowmun行数据就必须注意了。
SQL> select rownum ,id,name from student order by name;

可以看出,rownum并不是按照name列来生成的序号。系统是按照记录插入时的顺序给记录排的号,rowid也是顺序分配的。为了解决这个问题,必须使用子查询
SQL> select rownum ,id,name from (select * from student order by name);

参考文档:http://blog.csdn.net/mantisxf/article/details/1684805

3 mysql中的limit用法

具体的语法为:

SELECT * FROM table  LIMIT [offset,] rows | rows OFFSET offset
    LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。 
mysql> SELECT * FROM table LIMIT 5,10;  // 检索记录行 6-15

//为了检索从某一个偏移量到记录集的结束所有的记录行,可以指定第二个参数为 -1: 
mysql> SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-last.

//如果只给定一个参数,它表示返回最大的记录行数目: 
mysql> SELECT * FROM table LIMIT 5;     //检索前 5 个记录行

//换句话说,LIMIT n 等价于 LIMIT 0,n。
参考文档: http://www.phpweblog.net/peiyinjin/archive/2008/04/15/3199.html


4 mysql的高效分页写法

Select a.* from ( 
  select id from table b force index(ind_group_type_time) 
  where b.id=1111 order by b.update_time desc limit  xx, xx
) b, table a where a.id=b.id;

MySQL的limit工作原理就是先读取n条记录,然后抛弃前n条,读m条想要的,所以n越大,性能会越差。 

优化前SQL: SELECT * FROM member ORDER BY last_active LIMIT 50,5 

优化后SQL: SELECT * FROM member INNER JOIN (SELECT member_id FROM member ORDER BY last_active LIMIT 50, 5) USING (member_id) 

分别在于,优化前的SQL需要更多I/O浪费,因为先读索引,再读数据,然后抛弃无需的行。而优化后的SQL(子查询那条)只读索引(Cover index)就可以了,然后通过member_id读取需要的列。

参考文档:http://willko.iteye.com/blog/670120

5 分页写法的页数计算

总页数=(总记录数-1)/每页显示的记录数 +1

关于SQL Server SQL语句查询分页数据

比如:要求选取 tbllendlist 中 第3000页的记录,每一页100条记录。


select top 100 * from tbllendlist

where fldserialNo not in

(

select top 300100 fldserialNo from tbllendlist

order by fldserialNo

)

order by fldserialNo


或者:

SELECT TOP 100 *

FROM tbllendlist

WHERE (fldserialNo >

(SELECT MAX(fldserialNo)

FROM (SELECT TOP 300100 fldserialNo

FROM tbllendlist

ORDER BY fldserialNo) AS T))

ORDER BY fldserialNo


方法1执行速度比较快!

7 sqlserver2005的分页写法

SELECT ... 
    FROM 
        (
            SELECT ROW_NUMBER() OVER (ORDER BY ID asc) AS RowNum, ......
            FROM TABLE_NAME 
        ) AS T
    WHERE T.RowNum> 10  and T.RowNum<= 20
或者
WITH DataList AS 
    ( 
        SELECT ROW_NUMBER() OVER (ORDER BY O.ID DESC)AS RowNum, ......
        FROM .....
        WHERE ......
    )
    SELECT ......
    FROM DataList
    WHERE RowNum BETWEEN 10 AND 20

参考文档:http://callan.iteye.com/blog/422822

strut标签实现分页

<form name="form" method="post" action="peopleAction.do">
<table width="400" height="20" border="0" align="center" cellpadding="0" cellspacing="0">
<tr>
<td width="400" valign="middle" bgcolor="#CCCCCC">&nbsp;&nbsp;
        共为<bean:write name="maxPage"/>  <!-- 输出总记录数 -->
        页&nbsp;
        共有<bean:write name="pageNumber"/>     <!-- 输出总分页 -->       
        条&nbsp;
        当前为第<bean:write name="nonce"/>页 &nbsp;    <!-- 输出当前页数 -->
        <logic:equal name="nonce" value="1">
          首页
        </logic:equal>             
        <logic:notEqual name="nonce" value="1"> <!-- 如果当前页码不等于1 -->
           <a href="people.do?i=0">首页</a> <!-- 提供首页超链接 -->
        </logic:notEqual>
            &nbsp;
        <logic:lessEqual name="maxPage" value="${nonce}">   <!-- 如果当前页码不小于总页数 -->
            尾页                              <!-- 不提供尾页超链接 -->
        </logic:lessEqual>
        <logic:greaterThan name="maxPage"  value="${nonce}"> <!-- 如果当前页码小于总页数 -->
        <a href="people.do?i=<%=maxPage-1%>">尾页</a>&nbsp;   <!-- 提供尾页超链接 -->
        </logic:greaterThan> 
        <logic:equal name="nonce"  value="1">   <!-- 如果当前页码等于1 -->
            上一页                             <!-- 不提供上一页超链接 -->
        </logic:equal>
        <logic:notEqual name="nonce"  value="1">    <!-- 如果当前页码不等于1 -->
            <a href="people.do?i=<%=number-1%>">上一页</a>   <!-- 提供上一页超链接 -->
        </logic:notEqual>
        <logic:lessEqual name="maxPage" value="${nonce}">
            下一页
        </logic:lessEqual>
            <logic:greaterThan name="maxPage" value="${nonce}"> <!-- 如果当前页面小于总页数 -->
                <a href="people.do?i=<%=number+1%>">下一页</a>   <!-- 提供下一页超链接 -->
        </logic:greaterThan>           
    </td>
</tr>
</table>
</form>

9 Hibernate实现分页查询的原理分析

Hibernate 可以实现分页查询,例如: 

从第2万条开始取出100条记录 

Query q = session.createQuery("from Cat as c");;  
q.setFirstResult(20000);;  
q.setMaxResults(100);;  
List l = q.list();;

那么Hibernate底层如何实现分页的呢?实际上Hibernate的查询定义在net.sf.hibernate.loader.Loader这个类里面,仔细阅读该类代码,就可以把问题彻底搞清楚。 

Hibernate2.0.3的Loader源代码第480行以下: 

if (useLimit); sql = dialect.getLimitString(sql);;        
PreparedStatement st = session.getBatcher();.prepareQueryStatement(sql, scrollable);;
public boolean supportsLimit(); {  
  return true;  
}  
public String getLimitString(String sql); {  
  StringBuffer pagingSelect = new StringBuffer(100);;  
  pagingSelect.append(sql);;  
  pagingSelect.append(" limit ?, ?");;  
  return pagingSelect.toString();;  
}

这是net.sf.hibernate.dialect.MySQLDialect,MySQL的专用分页语句,再来看net.sf.hibernate.dialect.Oracle9Dialect: 


public boolean supportsLimit(); {  
  return true;  
}  
  
public String getLimitString(String sql); {  
  StringBuffer pagingSelect = new StringBuffer(100);;  
  pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");;  
  pagingSelect.append(sql);;  
  pagingSelect.append(" ); row_ where rownum <= ?); where rownum_ > ?");;  
  return pagingSelect.toString();;  
}



Oracle采用嵌套3层的查询语句结合rownum来实现分页,这在Oracle上是最快的方式,如果只是一层或者两层的查询语句的rownum不能支持order by 

除此之外,InterbasePostgreSQLHSQL也支持分页的sql语句,在相应的Dialect里面,大家自行参考。 

如果数据库不支持分页的SQL语句,那么根据在配置文件里面 

#hibernate.jdbc.use_scrollable_resultset true 

默认是true,如果你不指定为false,那么Hibernate会使用JDBC2.0scrollable result来实现分页,看Loader430行以下: 

if ( session.getFactory();.useScrollableResultSets(); ); {  
  // we can go straight to the first required row  
  rs.absolute(firstRow);;  
}  
else {  
  // we need to step through the rows one row at a time (slow);  
  for ( int m=0; m<firstRow; m++ ); rs.next();;  
}

如果支持scrollable result,使用ResultSetabsolute方法直接移到查询起点,如果不支持的话,使用循环语句,rs.next一点点的移过去。 

可见使用Hibernate,在进行查询分页的操作上,是具有非常大的灵活性,Hibernate会首先尝试用特定数据库的分页sql,如果没用,再尝试Scrollable,如果不行,最后采用rset.next()移动的办法。 

在查询分页代码中使用Hibernate的一大好处是,既兼顾了查询分页的性能,同时又保证了代码在不同的数据库之间的可移植性。

参考文档: http://www.iteye.com/topic/261


总结一下:数据库中mysql和oracle的分页写法都不一致,各个数据库有各自的特点。另外要注意下相关sql的性能优化,特别是针对大数据的翻页查询。



© 著作权归作者所有

共有 人打赏支持
zimingforever
粉丝 144
博文 265
码字总数 300605
作品 0
杭州
程序员
私信 提问
千万条数据,Stack Overflow 是如何实现快速分页的?

Stack Overflow 在分页机制中使用页码代替偏移量,页码指向基于 LIMIT 和 OFFSET 的查询。假设要对 1000 万条记录进行分页,跳到最后一页会非常慢,但 Stack Overflow 还是想办法实现了快速分...

周其
2018/05/02
2.5K
13
分布式数据库架构--排序、分页、分组、实现

最近研究分布式数据库架构,发现排序、分组及分页让着实人有点头疼。现把问题及解决思路整理如下。 一、 多分片(水平切分)返回结果合并(排序) 1、Select + None Aggregate Function的有序...

诺灬晓月
2014/09/18
0
0
千万条数据,Stack Overflow是如何实现快速分页的?

点击上方“程序员小灰”,选择“置顶公众号” 有趣有内涵的文章第一时间送达! 本文转载自公众号 聊聊架构 作者 Haney编辑 无明 Stack Overflow 在分页机制中使用页码代替偏移量,页码指向基...

bjweimengshu
2018/05/04
0
0
【MongoDB系列】:MongoDB 查询应用

find介绍 MongoDB中使用find来进行查询。查询就是返回一个集合中文档的子集,子集得范围从0个文档到整个集合。find得第一个参数决定要返回哪些文档,这个参数是一个文档,用于指定查询条件。...

hanfeng
2015/12/03
50
0
Spring Boot 开发REST接口最佳实践

HTTP动词与SQL命令对应 GET POST PUT DELETE PATCH URL中的约定 URL中名词使用复数形式 URL中的名称是使用单数还是使用复数的问题,争议由来已久。URL中的名词一般对应数据库中的表,表中存储...

固安李庆海
2017/12/31
0
0

没有更多内容

加载失败,请刷新页面

加载更多

极路由4刷OpenWrt(LEDE)

申请开发者模式 登录路由器后台 进入插件中间 选中路由器信息 申请开发者权限 安装开发者插件 刷 Bootloader 下载 极路由4 的 Breed 通过SSH登录路由器 (必须安装开发者插件,端口 1022 账号...

dingdayu
32分钟前
3
0
浅淡个人学习嵌入式Linux过程

我专业是电子信息工程,在初入大学的时候,我们的班主任便要我们多多去了解一些关于电子方面的知识。后来我了解到了嵌入式,继而了解到了嵌入式Linux。其实我们学习linux差不多就学习linux内...

linux-tao
今天
7
0
SpringBoot使用GraphQL简单学习-1

官网 一、GraphQL简介 1.GraphQL是什么? GraphQL 既是一种用于 API 的查询语言也是一个满足你数据查询的运行时。 GraphQL 对你的 API 中的数据提供了一套易于理解的完整描述,使得客户端能够...

wind2012
今天
5
0
Android的WIFI局域网对讲机

https://blog.csdn.net/z979451341/article/details/79280749 (三)Android局域网内语音对讲 基于UDP语音传输 https://blog.csdn.net/stormxiaofeng/article/details/80513947 Android7.0手......

shzwork
今天
2
0
vuex

一直有个误区 vuex既然页面刷新会丢失 那还有什么意义 。 重新翻看了下文档才恍然大误,vuex主要解决的是不同组件间的通信。 跨页面数据共享本质上还是用sessionStorage/localStorage...

东东笔记
今天
3
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部