sql真分页

编辑器加载中...use master go --创建数据库 if exists(select * from sys.databases where name='SMS') drop database SMS go create database SMS go --用Student数据库 use SMS --创建学生信息表 create table StuInfo ( S_ID char(6) not null primary key, --学生学号 S_Class char(5) not null, --班级名称 S_Name varchar(10) not null, --学生姓名 S_Sex bit default(0), --学生性别 S_Age int, --学生年龄 S_Address varchar(50) --学生住址 ) insert into StuInfo select '060101','J0601','周杰伦',1,'20','武汉' union select '060102','J0601','王心凌',0,'20','北京' union select '060103','J0601','张学友',1,'20','武汉' union select '060104','J0601','郑源',1,'20','广州' union select '060105','J0601','梁静茹',0,'20','广州' union select '060106','J0601','林俊杰',1,'20','深圳' union select '060107','J0601','张信哲',1,'20','广州' union select '060201','J0602','潘玮柏',1,'20','北京' union select '060202','J0602','张韶涵',0,'20','武汉' union select '060203','J0602','光良',1,'20','北京' union select '060204','J0602','谭咏麟',1,'20','北京' union select '060205','J0602','王杰',1,'20','广州' union select '060206','J0602','刀郎',1,'20','武汉' union select '060207','J0602','陈楚生',1,'20','上海' union select '060301','J0603','伍佰',1,'20','北京' union select '060302','J0603','陶喆',1,'20','北京' union select '060303','J0603','周杰伦',1,'20','上海' union select '060304','J0603','周华健',1,'20','深圳' union select '060305','J0603','蔡依林',0,'20','广州' union select '060306','J0603','胡彦斌',0,'20','武汉' union select '060307','J0603','刘若英',0,'20','广州' union select '060308','J0603','刘德华',1,'20','上海' union select '060309','J0603','王力宏',1,'20','武汉' go select * from StuInfo go --方法一： --得到编号从m到n的记录 --create proc usp_GetPagedStuInfo -- @StartIndex int,--开始编号 -- @EndIndex int--结束编号 --as --begin -- with temp as (select row_number() over(order by S_id asc) as RowNum ,* from StuInfo) -- select * from temp where RowNum between @StartIndex and @EndIndex --end go go --方法二： create proc usp_GetPagedStuInfo @PageSize int,--代表每页显示的记录数 @CurrentPageIndex int--代表当前是第几页:起始为1 as begin select top (@PageSize) * from StuInfo where S_ID not in(select top (@PageSize*(@CurrentPageIndex-1)) S_ID from StuInfo) end go --取出第1页的记录 exec usp_GetPagedStuInfo 5,1 --取出第2页的记录 exec usp_GetPagedStuInfo 5,2 --取出第3页的记录 exec usp_GetPagedStuInfo 5,3 --取出第4页的记录 exec usp_GetPagedStuInfo 5,4 --取出第5页的记录 exec usp_GetPagedStuInfo 5,5 --得到记录的总数 create proc usp_GetStuInfoCount as begin declare @count int select @count=count(*) from StuInfo return @count end go exec usp_GetStuInfoCount

转载于:https://www.cnblogs.com/rc727512646/archive/2012/07/03/2575470.html