分页

 

create database DBPage
use DBpage

create table pagetable
(
id int identity(1,1) not null,
col1 int null,
col2 nvarchar(50) null,
col3 datetime null
)

declare @i int
set @i=0
while(@i<100)
begin
insert into pagetable select cast(floor(rand()*100) as int),left(newid(),10),getdate()
set @i=@i+1
end

select * from pagetable

--每页显示十条。看第九页数据 10*8=80 pagesize*pageindex

--top
--1.select top pagesize *from table where id not in (select top pagesize*pageindex id from table)
--pageindex+1为页数，pagesize为每页显示的条数
select top 10 *from pagetable where id not in
(select top 80 id from pagetable)

select top 50 * from pagetest where id not in (select top 9900 id from pagetest order by id)order by id

--row_number()
--2.select * from (select row_number() over(order by id) num,* from table) a
--where a.num>pagesize*pageindex and a.num<pagesize*(pageindex+1)+1
select * from
(select row_number() over(order by id) rownum,* from pagetable) a
where a.rownum>80 and a.rownum<91

--top/max模式
--3.select top pagesize * from table where id>
--(select max(id) from (select top pagesize*pageindex id from table order by id)a) order by id
select top 10 * from pagetable where id>
(select max(id) from (select top 80 id from pagetable order by id)a)
order by id

--4.
select top 10 * from pagetable where not exists
(select 1 from (select top 80 id from pagetable order by id)a 
where a.id=pagetable.id)order by id

 

另外这里把分页写成的存储过程，功能很强大

http://www.cnblogs.com/hertcloud/archive/2005/09/29/246702.html