分页方案一:(利用Not In和SELECT TOP分页) 语句形式: 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大于多少和SELECT TOP分页) 语句形式: SELECT TOP 10 * FROM TestTable WHERE (ID > (SELECT MAX(id) FROM (SELECT TOP 20 id FROM TestTable ORDER BY id) AS T)) ----from 后的派生表一定要有一个别名. 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=@rowcount output 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语句 分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句 分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用在实际情况中,要具体分析。我的具体情况:select top 10 * from people where [id] > (select max([id]) from (select top 100000 [id] from people order by [id]) a) order by [id] SQL Server 执行时间: CPU 时间 = 100 毫秒,耗费时间 = 101 毫秒。select top 10 * from people where [id] not in (select top 100000 [id] from people order by [id]) order by [id] SQL Server 执行时间: CPU 时间 = 359 毫秒,耗费时间 = 359 毫秒。 编制分页存储过程:CREATE procedure fy @rows int , @pages int as declare @stra varchar(300) --如果数据类型的长度不够,那么存储过程将不会执行.(造成空格的丢失,) declare @strb varchar(300) declare @strc varchar(300) declare @strd varchar(300) set @stra=' select top '+cast(@rows as varchar(10))+' * from people where [id] > ' set @strb=' (select max([id]) from (select top '+cast((@rows*(@pages-1)) as varchar(10))+' [id] from people order by [id] asc) a) ' set @strc=' order by [id] asc ' set @strd=' select top '+cast(@rows as varchar(10))+' * from people ' if @pages=1 begin exec(@strd) end else begin exec(@stra+@strb+@strc) end GO
语句形式:
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大于多少和SELECT TOP分页)
语句形式:
SELECT TOP 10 *
FROM TestTable
WHERE (ID >
(SELECT MAX(id)
FROM (SELECT TOP 20 id
FROM TestTable
ORDER BY id) AS T)) ----from 后的派生表一定要有一个别名.
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=@rowcount output
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语句
分页方案一:(利用Not In和SELECT TOP分页) 效率次之,需要拼接SQL语句
分页方案三:(利用SQL的游标存储过程分页) 效率最差,但是最为通用在实际情况中,要具体分析。我的具体情况:select top 10 *
from people
where [id] >
(select max([id])
from (select top 100000 [id] from people order by [id]) a)
order by [id]
SQL Server 执行时间:
CPU 时间 = 100 毫秒,耗费时间 = 101 毫秒。select top 10 *
from people
where [id] not in
(select top 100000 [id]
from people order by [id])
order by [id]
SQL Server 执行时间:
CPU 时间 = 359 毫秒,耗费时间 = 359 毫秒。
编制分页存储过程:CREATE procedure fy
@rows int ,
@pages int
as
declare @stra varchar(300) --如果数据类型的长度不够,那么存储过程将不会执行.(造成空格的丢失,)
declare @strb varchar(300)
declare @strc varchar(300)
declare @strd varchar(300)
set @stra=' select top '+cast(@rows as varchar(10))+' * from people where [id] > '
set @strb=' (select max([id]) from (select top '+cast((@rows*(@pages-1)) as varchar(10))+' [id] from people order by [id] asc) a) '
set @strc=' order by [id] asc '
set @strd=' select top '+cast(@rows as varchar(10))+' * from people '
if @pages=1
begin
exec(@strd)
end
else
begin
exec(@stra+@strb+@strc)
end
GO