Pagging 博客分类： DatabaseJavaBasic sqlderbyoraclemysql

1. Derby

http://db.apache.org/derby/faq.html#limit

Derby does not support the LIMIT syntax. However, Derby 10.4 added the ROW_NUMBER function and Derby 10.7 added the OFFSET and FETCH clauses.

Derby also supports limiting the number of rows returned by a query through JDBC. For example, to fetch the first 5 rows of a large table:

Statement stmt = con.createStatement(); 
stmt.setMaxRows(5); 
ResultSet rs = stmt.executeQuery("SELECT * FROM myLargeTable"); 

Some related tuning tips are available in this external article.

Starting with the 10.4.1.3 release Derby also supports limiting the number of rows using the ROW_NUMBER function.

For example, to fetch the first 5 rows of a large table:

SELECT * FROM ( 
    SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.* 
    FROM myLargeTable 
) AS tmp 
WHERE rownum <= 5; 

The ROW_NUMBER function can also be used to select a limited number of rows starting with an offset, for example:

SELECT * FROM ( 
    SELECT ROW_NUMBER() OVER() AS rownum, myLargeTable.* 
    FROM myLargeTable 
) AS tmp 
WHERE rownum > 200000 AND rownum <= 200005; 

For more information, refer to the ROW_NUMBER built-in function in the Derby Reference Manual (available from the Documentation page). Development notes are available on the OLAPRowNumber wiki page.

The LIMIT keyword is not defined in the SQL standard, and is currently not supported.

2. Oracle

http://www.cnblogs.com/hxw/archive/2005/09/11/234619.html

1.根据ROWID来分

select * from t_xiaoxi where rowid in(select rid from (select rownum rn,rid from(select rowid rid,cid from
t_xiaoxi  order by cid desc) where rownum<10000) where rn>9980) order by cid desc;

执行时间0.03秒

2.按分析函数来分

select * from (select t.*,row_number() over(order by cid desc) rk from t_xiaoxi t) where rk<10000 and rk>9980;

执行时间1.01秒

3.按ROWNUM来分

select * from(select t.*,rownum rn from(select * from t_xiaoxi order by cid desc) t where rownum<10000) where rn>9980; 

执行时间0.1秒

其中t_xiaoxi为表名称，cid为表的关键字段，取按CID降序排序后的第9981-9999条记录，t_xiaoxi表有70000多条记录
个人感觉1的效率最好，3次之，2最差

统计总数

select count(*) from myLargeTable;
select count(*) from (select * from myLargeTable);

3. MySQL

select * from myLargeTable limit m;
select * from myLargeTable limit m,n;
select count(*) from myLargeTable;

4. PostgreSQL

select * from myLargeTable  offset rowBegin limit size;
select count(*) from myLargeTable;
select count(*) from (select * from myLargeTable) as my_Large_Table;