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
