四种关于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

--得到总页数

Heart Shaped Usb 2 Ports HubUSB LAN Network SERVER Print PRINTER NAS STORAGE BT NEWHDMI to 2*DVI adapter connector with long life time OEM3.5 IDE to 2.5 IDE adapter fashion design suitable for laptop transmission cardWired USB Bluetooth wireless pc Wireless PC Adapter DongleNew 200W DC 12V to AC 220V USB car power inverter adapter 765 LPD-200 seriesNAS SAMBA Network Storage SATA/IDE FTP Lan Server USB2D link 7DBI antenna wireless lan card WLAN card external WPS button DSSS spread spectrumUSB A/F 90° DIP buckle outside connector RoHS compliant OEM adapter connectorCross Shaped Wooden 1Gb USB 2.0 Flash Memory DriveCool High Cylindrical Wooden 2Gb USB 2.0 Flash Memory DriveUSB 54M with antenna wireless lan card packet filtering wireless roaming DSSSUSB A/F long body DIP 90°(right angle L=19.25mm) connector RoHS compliant socket adapter connectorUSB 2.0 Data Charger Adapter Retractable Cable for CellphonePCI108M WLAN wireless lan card external WPS button widely coverage area2*RCA F to 2*RCA F abreast type adapter with long life time connector OEMDVI12+5 Male 180°heightening type finished plug connector pin OEM adapter connectorUSB 2.0 to Parallel Printer Adapter Cable Samsung 10P Male wire solder USB connector cellphone connector mobile connectorIEEE 1394 Firewire Cable 6P Male to Male See ThroughCool Rotatable 1Gb USB 2.0 Flash Storage DrivePCI -E USB3.0 2 ports card up to 5Gbps data transfer rate NECμPD720200 chip PCI cardFunny Bull Shaped 4 Ports USB HubGold Like Portable 4 Ports USB 2.0 Hub for PC LaptopTF USB Card ReaderIDE to SATA adapter card high-speed plug and play serial ATA standard transmission card2Gb USB 2.0 Flash Memory128Mb USB 2.0 Flash MemoryUSB A sinking SMT&DIP 180° RoHS compliant connector OEM adapter connectorSmart cable with various colors usb mobile devices charger card reader hangs giftPortable USB 2.0 3.5 IDE Hard Disk Drive Enclosure Case3.5 DC to 2*DC adapter connector stable performanceUSB 2.0 Flash Memory Drive Colorful Wooden 256MbNice See-through 1Gb USB 2.0 Flash Storage DriveWonderful Green Tank Like 4 Ports USB Hub for PC LaptopNew 70W DC 12V to AC 220V power adapter 768-120W DGL seriesUniversal laptop AC/DC adapter 100W Auto Car home AC DC power adapter charger laptop 761-100W.DGL3.5 DC head adapter connector stable performance18*BNC F to 25 pin Male HD VGA Video Audio Monitor CableMini 4P B/F SMT USB connector mini usb plug adapter cellphone connectorUSB B/F SMT connector plug OEM adapter RoHS compliant connectorSmart Black Trye Like 4 Ports USB 2.0 Hub for PC Laptop256Mb USB 2.0 Flash MemoryToy Bricks 4Gb USB 2.0 Flash Storage DriveUSB Cable for Printer Beige WhiteMini 8P Female SMT USB connector cellphone connector mini usb port socket adapterUSB A/F 90° DIP short body connector (right angle L=14.1mm) RoHS compliant OEM adapter connectorHDMI 19P/F 90° DIP three-row pin HDMI high definition data plug HDMI connectorUSB A/M to IPOD,LG,DP,NIMI8P,USB B/M adapter connector stable performanceMini 5P USB short body connector RoHS compliant mini usb plug adapter connector Replica lv Hangbags Replica Hangbags Hangbags replica handbags replica handbags replica handbags replica handbags usb cable usb cable usb cable usb cable usb flash drives