四种关于SQL查询信息分页的代

过程一：select top N条记 * from 文章 where id not in(select top M条记 id from 文章 order by id desc ) order by id desc

过程二：select top N条记 * from 文章 where id <(select min(id) from (select top M条记 id from 文章 order by id desc ) as tblTmp) order by id desc

过程三：在数据库中写语句

create database mydb

go

use mydb

go

create table news

(id varchar(50) primary key,

name varchar(50)

)

go

insert into news values('020','sss')

insert into news values('021','ttt')

insert into news values('022','uuu')

insert into news values('001','aaa')

insert into news values('002','bbb')

insert into news values('003','ccc')

insert into news values('004','aaa')

insert into news values('005','ddd')

insert into news values('006','eee')

insert into news values('007','fff')

insert into news values('008','ggg')

insert into news values('009','hhh')

insert into news values('010','iii')

insert into news values('011','jjj')

insert into news values('012','kkk')

insert into news values('013','lll')

insert into news values('014','mmm')

insert into news values('015','nnn')

insert into news values('016','ooo')

insert into news values('017','ppp')

insert into news values('018','qqq')

insert into news values('019','rrr')

go

select * from news

go

create proc proc_cursor --定义存储过程

@pagesize int, --每页有多少条数据

@pageindex int, --第几

@pagetotal int output --总页

as

begin

declare @total int,@start int,@end int,@id varchar(10),@name varchar(10),@i int

--定义几个变量，作用后面解

declare mycur scroll cursor

for

select * from news order by ID--定义一个滚动游

open mycur--打开游标

set @total = @@cursor_rows--得到总的记录

if @total> 0

begin

if @total % @pagesize = 0

set @pagetotal = @total / @pagesize

else

set @pagetotal = @total / @pagesize + 1

--得到总页

if @pageindex < 1 set @pageindex = 1

if @pageindex > @pagetotal

set @pageindex = @pagetotal

--检查输入页数，确保它在1到总页数之

set @start = (@pageindex-1)*@pagesize+1

--游标第一次移动到的位置，比如我们需要第1115条记录，那么这个参数的值是11

set @end= @pageindex*@pagesize

--游标第二次移动到的位置，如上，这个值应该是15

if @end > @total

set @end = @total--确保第二次移动不超过记录的总条

set @i=@start

while(@i<=@end)

begin

fetch absolute @i from mycur into @id,@name

print @id+' '+@name

set @i=@i+1

end

end

else

begin

set @pagetotal = 0

select top 0 * from news

end

close mycur--关闭游标

deallocate mycur--释放游标

end

go

declare @pagetotal int

exec proc_cursor 5,7, @pagetotal output

--执行存储过程

print ' '

print ''+convert(varchar(10),@pagetotal)+''

go

drop proc proc_cursor--删除存储过程

use master

go

drop database mydb--删除数据

go

然后你在直接调用就OK啦么

过程4

create table book(

id int IDENTITY (1, 1) NOT NULL ,

category varchar(20) not null,

name varchar(20) not null,

author varchar(20) ,

publish varchar(20)

);

insert into book (category,name,author,publish) values

('物理','物理练习一','李力','大连');

insert into book (category,name,author,publish) values

('物理','物理练习','王军','大连');

insert into book (category,name,author,publish) values

('物理','物理练习','李力','大连');

insert into book (category,name,author,publish) values

('物理','物理练习','王军','大连');

insert into book (category,name,author,publish) values

('英语','英语练习一','李力','大连');

insert into book (category,name,author,publish) values

('英语','英语练习','王军','大连');

insert into book (category,name,author,publish) values

('英语','英语练习','李力','大连');

insert into book (category,name,author,publish) values

('英语','英语练习','王军','大连');

insert into book (category,name,author,publish) values

('数学','数学练习一','李力','大连');

insert into book (category,name,author,publish) values

('数学','数学练习','李力','大连');

insert into book (category,name,author,publish) values

('数学','数学练习','李力','大连');

insert into book (category,name,author,publish) values

('数学','数学练习','李力','大连');

insert into book (category,name,author,publish) values

('数学','数学练习','李力','大连');

insert into book (category,name,author,publish) values

('数学','数学练习','李力','大连');

insert into book (category,name,author,publish) values

('数学','数学练习','李力','大连');

SELECT TOP 2 * FROM book WHERE (ID NOT IN(SELECT TOP 4 idFROM bookORDER BY id))

ORDER BY ID;

分页公式

select top 页大 * from 表名

where (id not in(select top ((页码-1)*页大) id from 表名 order by id)) order by id;

条件查询

select top 页大 * from table1 where category='数学'

and

(ID NOT IN (SELECT TOP ((页码-1)*页大) id FROM table1 ORDER BY id)) ORDER BY ID;