[技術] 各家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

If you enjoyed this post Subscribe to our feed

No Comment

張貼留言