四种关于SQL查询信息分页的代码[MSSQL防范]

历程一: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

--得到总页数