Java开发网 - [原创]从关系数据库中选取第m至第n行记录（申请版主给加一分）

Topic: [原创]从关系数据库中选取第m至第n行记录（申请版主给加一分）

1.[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分）

Posted by: hailwind
Posted on: 2003-11-14 15:30

我相信很多程序员朋友都遇到过这么一个问题，就是分页显示。

在做的时候，无论如何，我们都不应该把记录全部读到内存当中再选择需要的记录少了的话，还可以，要是表中有50万条记录呢？保证你会死掉

最好的方法：

我先问一个问题：真的会有很多时间需要支持很多种数据库的应用吗？不是吧？

如果的确真的是的话那么分别实现一套方法吧，然后用DAO模式来解决藕合度问题，把数据库操作的事情单拉出来吧！

下面给出Oracle/Sqlserver/MySQL三种数据库先出第m至第n条记录的方法：

获取按照F1字段升序排序的，从m至n行记录

注意，F1一定要做索引或主键，最好是主键

SqlServer

select t2.* from (Select top n-m t1.* from (Select top n * from Tablename as t order by t.F1) as t1 order by t1.F1 desc) as t2 order by t2.F1

select t2.* from
(
select top 20 t1.* from
(
select top 500020 * from dbgen as t order by t.F1
)
as t1 order by t1.F1 desc
)
as t2 order by t2.F1

Oracle

select * from (select rownum r, t.* from Tablename t where r<n) t1 where t1.r > m and t1.r <= m

select * from (select rownum r, t.* from DBTEST t where rownum<300020) ss where ss.r > 300000 and ss.r <= 300020

MySQL

最简单
select * from Tablename t order by t.F1 limit n,m-n+1

select * from DBtest t order by t.F1 limit 300000,20

性能对比：
三者差不多少！
上面实例中的语句在50万条记录的表中选取第50万左右的记录20条，均不超过1秒。

其它数据库我相信也能找到差不多的方法。

有错误之处，请指正

申请版主给加一分，嘿嘿。偶想看FTP

2.Re:[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分） [Re: hailwind]	Copy to clipboard
Posted by: epubis Posted on: 2003-11-15 04:23 我做查找的分页用mysql是先记录条件，然后看页数，根据页数计算查找范围，下次传送查找ID和页数，再次查找。想知道别人怎么做的

3.Re:[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分） [Re: epubis]	Copy to clipboard
Posted by: 牛老板 Posted on: 2003-11-15 11:28 sigh~~, Sybase 11.0.3 怎么办啊..

4.Re:[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分） [Re: hailwind]	Copy to clipboard
Posted by: why Posted on: 2003-11-15 21:17 related topic: 分页显示的SQL语句应该如何写 Please note snowbug's remarks: Oracle 用 order by 的话, rownum 还是对应着 order 以前的纪录，所以不是一个通用的解决办法。

5.Re:[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分） [Re: why]

Copy to clipboard

Posted by: hailwind
Posted on: 2003-11-17 14:11

why wrote:
Please note snowbug's remarks:
Oracle 用 order by 的话, rownum 还是对应着 order 以前的纪录，所以不是一个通用的解决办法。

对于这句话，我不是非常地能理解，rownum还是对应着以前的纪录的行号有问题吗？

但是absolute()方法肯定不是一个好方法，这个跟数据库与驱动程序有关，至少在MS的驱动程序上这个方法是不好用的。只要一执行，内存与CPU就会狂升。

下面贴上Hibernate中关于Oracle的这个方法：
引自：net.sf.hibernate.dialect.Oracle9Dialect类：

  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();
  }

说明：我说的这个思想与Hibernate的分页的思想是一样的，大家可以看一下它的源码。

6.Re:[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分） [Re: why]	Copy to clipboard
Posted by: ditty Posted on: 2003-11-17 14:24 why wrote: Oracle 用 order by 的话, rownum 还是对应着 order 以前的纪录，所以不是一个通用的解决办法。 I don't think so! 'rownum' is the order num for current query's every row!

7.Re:[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分） [Re: hailwind]	Copy to clipboard
Posted by: jfml Posted on: 2003-11-18 11:20 我先问一个问题：真的会有很多时间需要支持很多种数据库的应用吗？不是吧？ ----------------------------------------------------------------------------------------------------- 我现在就只能用标准SQL分页上百万的数据~

8.Re:[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分） [Re: jfml]	Copy to clipboard
Posted by: ybwang Posted on: 2003-11-18 12:57 可以拓展思路

9.Re:[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分） [Re: hailwind]	Copy to clipboard
Posted by: bjwz Posted on: 2003-11-26 12:23 我觉得这种需求如果合理，那为什么不把这加入到SQL的标准中。

10.Re:[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分） [Re: hailwind]

Copy to clipboard

Posted by: emate
Posted on: 2003-11-27 01:21

用　absolute　的时候　游标是可能回滚的，所以对于系统内存的开销特别大

，直接用TYPE_FORWARD_ONLY,

Stmt=Conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

然后用死方法，ｎｅｘｔ固定行数

或者实现就ｓｅｔ　ｒｏｗｃｏｕｔ　
再ｓｅｌｅｃｔ

补充一句　，Ｓｙｂａｓｅ是不支持　select top n 的

11.Re:[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分） [Re: emate]

Copy to clipboard

Posted by: zmhy
Posted on: 2003-11-27 12:15

emate wrote:
用　absolute　的时候　游标是可能回滚的，所以对于系统内存的开销特别大

，直接用TYPE_FORWARD_ONLY,

Stmt=Conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,java.sql.ResultSet.CONCUR_READ_ONLY);

然后用死方法，ｎｅｘｔ固定行数

或者实现就ｓｅｔ　ｒｏｗｃｏｕｔ　
再ｓｅｌｅｃｔ

补充一句　，Ｓｙｂａｓｅ是不支持　select top n 的

不对，sybase可以实现top n的功能，但和标准sql的不一样罢了。
语句为：
set rowcount 20 selelect * from ......就可以实现了

12.Re:[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分） [Re: hailwind]	Copy to clipboard
Posted by: slump Posted on: 2003-12-03 16:43 hibernate中使用的是3层嵌套SQL语句，rownum在第2层中，所以对应的是order by以后的记录，如果rownum在最内层语句，对应的是order by以前的记录，这样带order by的分页就有问题了

13.Re:[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分） [Re: hailwind]	Copy to clipboard
Posted by: merlin45 Posted on: 2003-12-04 17:25 db2 SELECT TEMP.* FROM (SELECT ROW_NUMBER() OVER(ORDER BY column1) AS ROWID , tx.* FROM tx) AS TEMP WHERE ROWID between 101 ANd 200 order by temp.ColumnX 不知道是否还有别的写法，DB2的SQL，比较生。

14.Re:[原创]从关系数据库中选取第m至第n行记录（申请版主给加一分） [Re: hailwind]	Copy to clipboard
Posted by: redmiddle Posted on: 2003-12-08 11:19 不知道大家是否有否自己测试过? 呵呵.我就试验过oracle.对于单一表的从n到m的选取记录的方式,确实快很多.但是如果是几个表join了.呵呵.基本上没有效果.甚至有的更加慢了. 每一种方法,在使用之前建议都实验一下.