四種關於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

--得到總頁數

關鍵詞：代碼 

• 1
• 2
• 3
• 下一頁