程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> 四種關於SQL查詢信息分頁的代碼

四種關於SQL查詢信息分頁的代碼

編輯:關於SqlServer


過程一: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. 下一頁:
Copyright © 程式師世界 All Rights Reserved