随着数据库的发展,如今的数据库可以储存大量的数据,内存也是越来越大,但是无论您的内存多大,内存总是显得不够用,这时就要涉及到分页,下文中将为大家带来详细的数据库分页操作。
1.Oracle:
1
2
|
select
*
from
(
select
row_.*, rownum rownum_
from
( query_SQL ) row_
where
rownum =<
max
)
where
rownum_ >=
min
|
2.SQL Server:
1
2
|
select
top
@pagesize *
from
tablename
where
id
not
in
(
select
top
@pagesize*(@page-1) id
from
tablename
order
by
id)
order
by
id
|
3.MySQL
1
|
select
*
from
tablename limit position, counter
|
4.DB2
1
2
|
select
*
from
(
select
*,rownumber()
as
ROW_NEXT
from
tablename)
where
ROW_NEXT
between
min
and
max
|
分页方案一:(利用Not In和SELECT TOP分页)效率次之
语句形式:
1
2
3
4
5
6
|
SELECT
TOP
10 *
FROM
TestTable
WHERE
(ID
NOT
IN
(
SELECT
TOP
20 id
FROM
TestTable ORDERBY id)) ORDERBYID
SELECT
TOP
页大小 *
FROM
TestTable
WHERE
( ID
NOT
IN
(
SELECT
TOP
每页大小-1*待查询页数-1 id
FROM
表
ORDERBY id)) ORDERBYID
|
思路:先查询出待查询页之前的全部条数的id,查询ID不在这些ID中的指定数量条数。
分页方案二:(利用ID大于多少和SELECT TOP分页)效率最高
语句形式:
1
2
3
4
5
6
|
SELECT
TOP
10 *
FROM
TestTable
WHERE
(ID>(
SELECT
MAX
(id)
FROM
(
SELECT
TOP20 id
FROM
TestTable ORDERBYid)
AS
T))ORDERBY ID
SELECT
TOP
页大小*
FROM
TestTable
WHERE
(ID>(
SELECT
MAX
(id)
FROM
(
SELECT
TOP
每页大小*待查询页数-1 id
FROM
表
ORDERBY id)
AS
T)) ORDERBY ID
|
思路:先获得待查询页的之前全部条数id,获得它们当中最大的ID号,以此最大ID号为标志,查找比这个ID号大的指定条数。
分页方案三:
1
2
3
4
|
SELECT
TOP
PageSize *
FROM
(
SELECT
TOP
nPage*PageSize *
from
YOURTABLE
order
by
id)
as
a
order
by
id
desc
SELECT
TOP
每页条数 *
FROM
(
SELECT
TOP
待查询页*每页条数) *
from
YOURTABLE
order
by
id)
as
a
order
by
id
desc
|
思路:先正排序查询出待查询页之前(包括当前页)的全部条数,然后将其倒排序,取指定条数。