假分页:从数据库中选择所有的记录后再进行分页
真分页:只从数据库中选择当前页的记录
- select top 每页显示的记录数 * from topic where id not in
- (select top (当前的页数-1)×每页显示的记录数 id from topic order by id desc)
- order by id desc
select top 每页显示的记录数 * from topic where id not in
(select top (当前的页数-1)×每页显示的记录数 id from topic order by id desc)
order by id desc
需要注意的是在access中不能是top 0,所以如果数据只有一页的话就得做判断了。。
SQL2005中的分页代码:
- with temptbl as (
- SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row,
- ...
- )
- SELECT * FROM temptbl where Row between @startIndex and @endIndex
with temptbl as (
SELECT ROW_NUMBER() OVER (ORDER BY id desc)AS Row,
...
)
SELECT * FROM temptbl where Row between @startIndex and @endIndex
分页存储过程:
- create procedure proc_pager
- ( @startIndex int,--开始记录数
- @endIndex int --结束记录数
- )
- as
- begin
- declare @indextable table(id int identity(1,1),nid int)
- insert into @indextable(nid) select orderid from orders order by orderid desc
- select *
- from orders o
- inner join @indextable i
- on o.orderid=i.nid
- where i.id between @startIndex and @endIndex
- order by i.id
- end
create procedure proc_pager
( @startIndex int,--开始记录数
@endIndex int --结束记录数
)
as
begin
declare @indextable table(id int identity(1,1),nid int)
insert into @indextable(nid) select orderid from orders order by orderid desc
select *
from orders o
inner join @indextable i
on o.orderid=i.nid
where i.id between @startIndex and @endIndex
order by i.id
end
- create procedure proc_pager1
- ( @pageIndex int, -- 要选择第X页的数据
- @pageSize int -- 每页显示记录数
- )
- AS
- BEGIN
- declare @sqlStr varchar(500)
- set @sqlStr='select top '+convert(varchar(10),@pageSize)+
- ' * from orders where orderid not in(select top '+
- convert(varchar(20),(@pageIndex-1)*@pageSize)+
- ' orderid from orders) order by orderid'
- exec (@sqlStr)
- END
create procedure proc_pager1
( @pageIndex int, -- 要选择第X页的数据
@pageSize int -- 每页显示记录数
)
AS
BEGIN
declare @sqlStr varchar(500)
set @sqlStr='select top '+convert(varchar(10),@pageSize)+
' * from orders where orderid not in(select top '+
convert(varchar(20),(@pageIndex-1)*@pageSize)+
' orderid from orders) order by orderid'
exec (@sqlStr)
END
下面这个仅适用sql2005