[技術] 各家Database分頁實作

Written on 11:25 上午 by Yu Lai

Ref: http://blog.blueshop.com.tw/gpx1981/archive/2007/08/24/52129.aspx

DB2
1.1. SELECT * FROM STUDENT ORDER BY SCORE DESC fetch first 10 rows only
1.2. select * from ( SELECT rownumber() over(ORDER BY SCORE DESC) as row_,
* FROM STUDENT ORDER BY SCORE DESC ) as temp_ where row_ <= 10
2. select * from ( SELECT rownumber() over(ORDER BY SCORE DESC) as row_,
* FROM STUDENT ORDER BY SCORE DESC ) as temp_ where row_ between 11
and 20

Firebird
1. SELECT first 10 * FROM student ORDER BY score DESC
2. SELECT first 10 SKIP 10 * FROM student ORDER BY score DESC
　
HypersonicSQL(HSQL)
1. SELECT TOP 10 * FROM student ORDER BY score DESC
2. SELECT LIMIT 10 10 FROM student ORDER BY score DESC
　
Interbase
1. SELECT * FROM student ORDER BY score DESC ROWS 10
2. SELECT * FROM student ORDER BY score DESC ROWS 10 TO 10
　
MySQL
1. SELECT * FROM student ORDER BY score DESC LIMIT 10
2. SELECT * FROM student ORDER BY score DESC LIMIT 10, 10
　
Oracle
1. select * from ( SELECT * FROM STUDENT ORDER BY SCORE DESC ) where
rownum <= 10
2. select * from ( select err.*, rownum rownum_ from ( SELECT * FROM
errormsg order by seqno) err where rownum <= 20) where rownum_ > 10
　
PostgreSQL
1. SELECT * FROM student ORDER BY score DESC limit 10
2. SELECT * FROM student ORDER BY score DESC limit 10 OFFSET 10
　
SQLServer
1. SELECT top 10 * FROM STUDENT ORDER BY SCORE DESC
　
paged queries not supported
FrontBase, Informix, Ingres, Mckoi, NoArgSQL, Pointbase, Progress, SAPDB,
StandardSQL, Sysbase

Posted in 技術, Database, SQL

If you enjoyed this post Subscribe to our feed