PAGESIZE: | 每页显示的记录数 |
CURRENTPAGE: | 当前页号 |
数据表的名字是: | components |
索引主键字是: | id |
ORACLE:
利用rownum关键字
--【1】
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
--【2】
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40
--
【2】比【1】低效
--如果不介意在系统中使用HINT的话,分页的查询语句为:
SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21
--
利用not in 关键字
select
* from components
where id not in
(
select
id from components
where
rownum<=(PAGESIZE*(CURRENTPAGE-1))
)
and rownum<=PAGESIZE
order by id
--
利用minus关键字
select
* from components
where
rownum<=(PAGESIZE*(CURRENTPAGE-1))
minus
select
* from components
where
rownum<=(PAGESIZE*(CURRENTPAGE-2))
SQLSERVER,ACCESS:
select
top PAGESIZE *
from components
where id not in
(
select
top (PAGESIZE*(CURRENTPAGE-1)) id
from components
order by id
)
order by id
MYSQL:
select
*
from table
[查询条件]
order by id
limit ?,?
LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初 始记录行的偏移量是 0(而不是 1)。
例:
SELECT * FROM table LIMIT 5,10; // 检索记录行为6-15
分页工具类:
1:commons-java-pager是一个开源项目
下载地址:http://code.google.com/p/commons-java-pager/downloads/list
2:页面数据分页处理 SmartPagination
下载地址:http://code.google.com/p/smartpagination/downloads/list
内容自己整理代码来自互联网