数据库的分页--根据网上整理

在SQL Server中通过SQL语句实现分页查询

建立表：

CREATE TABLE [TestTable] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

插入数据：(2万条，用更多的数据测试会明显一些)
SET IDENTITY_INSERT TestTable ON

declare @i int
set @i=1
while @i<=20000
begin
insert into TestTable([id], FirstName, LastName, Country,Note) values(@i,'FirstName_XXX','LastName_XXX','Country_XXX','Note_XXX')
set @i=@i+1
end

SET IDENTITY_INSERT TestTable OFF



-------------------------------------

分页方案一：(利用Not In和SELECTTOP分页)
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 20 id
FROM TestTable
ORDER BY id))
ORDER BY ID


SELECT TOP 页大小 *
FROM TestTable
WHERE (ID NOT IN
(SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id))
ORDER BY ID

-------------------------------------

分页方案二：(利用ID大于多少和SELECTTOP分页）
语句形式：
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T))
ORDER BY ID


SELECT TOP 页大小 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 页大小*页数 id
FROM 表
ORDER BY id) AS T))
ORDER BY ID


-------------------------------------

分页方案三：(利用SQL的游标存储过程分页)
create procedure XiaoZhengGe
@sqlstr nvarchar(4000), --查询字符串
@currentpage int, --第N页
@pagesize int --每页行数
as
set nocount on
declare @P1 int, --P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcountoutput
select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpageas 当前页
set @currentpage=(@currentpage-1)*@pagesize+1
exec sp_cursorfetch @P1,16,@currentpage,@pagesize
exec sp_cursorclose @P1
set nocount off

其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。
建议优化的时候，加上主键和索引，查询效率会提高。

通过SQL 查询分析器，显示比较：我的结论是:
分页方案二：(利用ID大于多少和SELECTTOP分页）效率最高，需要拼接SQL语句
分页方案一：(利用Not In和SELECTTOP分页) 效率次之，需要拼接SQL语句
分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用

在实际情况中，要具体分析。

一、DB2:

DB2分页查询

SELECT* FROM (Select 字段1,字段2,字段3,rownumber()over(ORDER BY 排序用的列名 ASC) AS rnfrom 表名) AS a1 WHEREa1.rn BETWEEN 10 AND 20

以上表示提取第10到20的纪录

select* from (select rownumber() over(order by id asc ) as rowid from table whererowid <=endIndex ) where rowid > startIndex

如果Order By 的字段有重复的值，那一定要把此字段放到 over()中

select* from ( select ROW_NUMBER() OVER(ORDER BY DOC_UUID DESC) AS ROWNUM, DOC_UUID,DOC_DISPATCHORG, DOC_SIGNER, DOC_TITLE from DT_DOCUMENT ) a where ROWNUM >20 and ROWNUM <=30

增加行号，不排序

select* from ( select ROW_NUMBER() OVER() AS ROWNUM,t.* from DT_DOCUMENT t ) a

增加行号，按某列排序

select* from ( select ROW_NUMBER() OVER( ORDER BY DOC_UUID DESC ) AS ROWNUM,t.* fromDT_DOCUMENT t ) a

二、Mysql:

最简单

select* from table limit start,pageNum

比如从10取20个数据

select* from table limit 10,20

三、Oracle：

select* from (select rownum,name from table where rownum <=endIndex ) where rownum> startIndex

例如从表Sys_option(主键为sys_id)中从10条记录还是检索20条记录，语句如下：

SELECT*

FROM(SELECT ROWNUM R,t1.* From Sys_option where rownum < 30 ) t2

Wheret2.R >= 10

四、 sql server:

分页方案一：(利用NotIn和SELECT TOP分页) 语句形式：

SELECTTOP 10 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 20 id FROM TestTable ORDERBY id)) ORDER BY ID

SELECTTOP 页大小 * FROMTestTable WHERE (ID NOT IN

(SELECTTOP 页大小*页数 id FROM 表

ORDERBY id)) ORDER BY ID

-------------------------------------

分页方案二：(利用ID大于多少和SELECT TOP分页） 语句形式：

SELECTTOP 10 * FROM TestTable WHERE (ID >

(SELECTMAX(id)

FROM(SELECT TOP 20 id FROM TestTable

ORDERBY id) AS T)) ORDER BY ID

SELECTTOP 页大小 * FROMTestTable WHERE (ID >

(SELECTMAX(id)

FROM(SELECT TOP 页大小*页数 id FROM 表

ORDERBY id) AS T)) ORDER BY ID

-------------------------------------

分页方案三：(利用SQL的游标存储过程分页) create procedure XiaoZhengGe

@sqlstrnvarchar(4000), --查询字符串 @currentpageint, --第N页 @pagesize int --每页行数 as

setnocount on

declare@P1 int, --P1是游标的id @rowcountint

execsp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcountoutput select ceiling(1.0*@rowcount/@pagesize) as 总页数--,@rowcount as 总行数,@currentpage as 当前页

set@currentpage=(@currentpage-1)*@pagesize+1 exec sp_cursorfetch@P1,16,@currentpage,@pagesize exec sp_cursorclose @P1 set nocount off

其它的方案：如果没有主键，可以用临时表，也可以用方案三做，但是效率会低。 建议优化的时候，加上主键和索引，查询效率会提高。

通过SQL 查询分析器，显示比较：我的结论是:

分页方案二：(利用ID大于多少和SELECT TOP分页）效率最高，需要拼接SQL语句 分页方案一：(利用NotIn和SELECT TOP分页) 效率次之，需要拼接SQL语句 分页方案三：(利用SQL的游标存储过程分页) 效率最差，但是最为通用

几种sqlserver2005高效分页sql查询语句

top方案：

sql codeselect top 10 * from table1

 where id not in(select top 开始的位置 id fromtable1)

 

max:

 sqlcodeselect top 10 * from table1

 where id>(select max(id)

 from(select top 开始位置 id from table1order by id)tt)

 

row:

 sqlcodeselect *

 from(

    select row_number()over(order by tempcolumn)temprownumber,*

    from (select top 开始位置+10 tempcolumn=0,* from table1)t

 )tt

 where temprownumber>开始位置

3种分页方式，分别是max方案，top方案，row方案

 

效率：

  第1：row

   第2：max

   第3：top

 

缺点：

  max：必须用户编写复杂sql，不支持非唯一列排序www.111cn.net

  top：必须用户编写复杂sql,不支持复合主键

  row：不支持sqlserver2000

 

测试数据：

 共320万条数据，每页显示10条数据，分别测试了2万页、15万页和32万页。

 

页码，top方案，max方案，row方案

2万，60ms，46ms，33ms

 15万，453ms，343ms，310ms

 32万，953ms，720ms，686ms

 

 

是一种通过程序拼接sql语句的分页方案，

 用户提过的sql语句不需要编写复杂的sql逻辑

 

诺用户提供sql如下

sql code

 select * from table1

 

 

 

从第5条开始，查询5条，处理后sql变为

sql code

 select *

 from(

    select row_number()over(order by tempcolumn)temprownumber,*

    from (select top 10 tempcolumn=0,* from table1)t

 )tt

 where temprownumber>5

 

 

 

这是什么意思呢？分解一下

 

首先将用户输入的sql语句转稍稍修改

 在select后添加top 开始位置+条数变成

 再外加一列tempcolum,变成这样

sql code

 select top 20 tempcolumn=0,* from clazz

 

 

嵌套一层，这样便可查询出行号www.111cn.net

刚才那个列就是用来这里order by用的

 （也不知道sqlserver的row_number函数为什么必须要orderby）

sql code

 select row_number()over(order bytempcolumn)temprownumber,*

 from(修改过的查询)t

 

 

再套一层，过滤掉行号小于开始位置的行

sql code

 select * from (第二层)tt

 where temprownumber>10

 

更多详细内容请查看：http://www.111cn.net/database/mssqlserver/33578.htm

Oracle, SQL Server, My SQL数据分页查询语句

经常在网站上看到“上一页/下一页”，实现方法主要是每次到数据库中得到当前页面的数据，下面总结Oracle, SQL Server 和MySQL的分页SQL语句如下：

Oracle：

    方法一：
    SELECT * FROM
    (
        SELECT A.*, ROWNUM RN
        FROM (SELECT * FROM TABLE_NAME) A
        WHERE ROWNUM <= 40
    )
    WHERE RN >= 21；
    方法二：
    SELECT * FROM
    (
        SELECT A.*, ROWNUM RN
        FROM (SELECT * FROM TABLE_NAME) A
    )
    WHERE RN between 21 and 40

    公认第二种方法效率没有第一种高。原因是第二种要把子查询执行完，而第一种方法子查询执行到Rownum=40后就结束了。

MySQL:

    SELECT * FROM TABLE_NAME LIMIT 10, 20
    表示从第11条数据开始取20条数据返回，limit后的2个参数含义为：起点和步长，即从那条数据开始，取多少条数据，再如取前20条数据：SELECT * FROM TABLE_NAME LIMIT 0, 20

SQL Server2000：

    SELECT TOP @pagesize * FROM TABLE_NAME WHERE id not in(SELECT TOP @pagesize*(@page-1) id FROM TABLE_NAME ORDER BY id) ORDER BY id

SQL Server 2005：

    方法一：

    SELECT ...
    FROM
        (
            SELECTROW_NUMBER() OVER (ORDER BY ID asc) AS RowNum, ......
            FROMTABLE_NAME
        ) AS T
    WHERE T.RowNum> 10 and T.RowNum<= 20

    方法二：

    WITH DataList AS
    (
        SELECT ROW_NUMBER() OVER (ORDER BYO.ID DESC)AS RowNum, ......
        FROM .....
        WHERE ......
    )

    SELECT ......
    FROM DataList
    WHERE RowNum BETWEEN 10 AND 20

SQL Server数据库用sql语句实现分页查询 (从M条数据开始，查找N条记录。sqlserver数据库。请举例说明。)

2011-01-03 00:13亨利贞 | 分类：数据库DB | 浏览2403次

从M条数据开始，查找N条记录。sqlserver数据库。请举例说明。有多种方法更好

 

create table t1(id int identity primary key,name varchar(16))declare i intset i = 0while(i<30)begin insert into ti values ('name'+i)set i=i+1end-------------------------------例如m=5,N=10select top 10 * from ti where id not in (select top 5 id from ti)