【SQL教程】sql Sever 创建分页储存过程及直接查询
--创建存储过程
if exists(select * from sysobjects where name='P_GetStudentInfoPage')
drop proc P_GetStudentInfoPage
go
create proc .P_GetStudentInfoPage
(
@pageIndex int=1,--页号
@pageSize int=10--每页数据条数
)
as
--select count(*) from StudentInfo--查询数据总数
select * from
(select ROW_NUMBER() over(order by StudentNo) as rowNum,* from StudentInfo) as tbl
where tbl.rowNum between ((@pageIndex - 1)* @pageSize + 1) and (@pageIndex*@pageSize)
GO
--调用
EXEC P_GetStudentInfoPage 1,10
EXEC P_GetStudentInfoPage 2,10
--------------------------------------直接查询代码
select * from StudentInfo where id>=1 and id<=10
select * from StudentInfo where id>=10 and id<=20
select * from StudentInfo where id between (1-1)*10+1 and 10 *1
select * from StudentInfo where id between (2-1)*10+1 and 10 *2
select (1-1)*10+1 --1
select (2-1)*10+1 --11
select (3-1)*10+1 --21
select * from StudentInfo where id between 11 and 20
select * from
(select ROW_NUMBER() over(order by StudentNo desc) newRows,* from StudentInfo) t
where t.newRows between (1-1)*10+1 and 10 *1
页:
[1]