SQLServer分页查询
建表参考：SQLServer脚本建库表
写法1：top ... not in ...
select top 5 * from student -- 查询前5行学生记录
select * from student
where s_id not in ('ST001','ST004') -- 查询除了'ST001','ST004'的学号以外的学生记录
select top 5 * from student -- 页
where s_id not in (select top 0 s_id from student order by s_id)
select top 5 * from student -- 第二页
where s_id not in (select top 5 s_id from student order by s_id)
select top 5 * from student -- 第三页
where s_id not in (select top 10 s_id from student order by s_id)
-- 使用变量：页码 pageIndex , 每页大小 pageSize
declare @pageIndex int = 1
declare @pageSize int = 5
select top (@pageSize) * from student
where s_id not in (select top ((@pageIndex-1)*@pageSize) s_id from student order by s_id)
写法2（推荐）：row_number() 行号 over(排序列)
select ROW_NUMBER() over(order by s_id) as 行号, * from student -- 为查询的记录集输出行号
select * from
(
select ROW_NUMBER() over(order by s_id) as num, * from student
) t
where num between 1 and 5 -- 页，从第1行到第5行
select * from
(
select ROW_NUMBER() over(order by s_id) as num, * from student
) t
where num between 6 and 10 -- 第二页，从第6行到第10行
-- 使用变量：页码 pageIndex2 , 每页大小 pageSize2
declare @pageIndex2 int = 1
declare @pageSize2 int = 5
select * from
(
select ROW_NUMBER() over(order by s_id) as num, * from student
) t
where num between ((@pageIndex2 - 1) * @pageSize2 + 1) and (@pageIndex2 * @pageSize2)
-- 计算公式：从 ((页码-1)*每页大小+1)行 到 (页码 * 每页大小)行
-- 使用row_number分页查询学生、课程、成绩
-- 步：连接查询
select row_number() over(order by a.s_id) as 行号,
a.s_id as 学号,
c.c_id as 课程号,
s_name as 姓名,
c_name as 课程名称,
sc_score as 分数
from student a , score b, course c
where a.s_id = b.s_id
and b.c_id = c.c_id
-- 第二步：分页查询
select * from
(
select row_number() over(order by a.s_id) as 行号,
a.s_id as 学号,
c.c_id as 课程号,
s_name as 姓名,
c_name as 课程名称,
sc_score as 分数
from student a , score b, course c
where a.s_id = b.s_id
and b.c_id = c.c_id
) t
where 行号 between 1 and 3
-- 第三步：分页带条件查询
select * from
(
select row_number() over(order by a.s_id) as 行号,
a.s_id as 学号,
c.c_id as 课程号,
s_name as 姓名,
c_name as 课程名称,
sc_score as 分数
from student a , score b, course c
where a.s_id = b.s_id
and b.c_id = c.c_id
and sc_score > 70 -- 查询条件，成绩>70
) t
where 行号 between 1 and 3
把分页算法封装到数据库的存储过程
作用：重用、实现更多的功能（可以获得总行数、总页数）
-- 基本分页存储过程：分页查询每个学生的成绩记录
create proc sp_scorepage
(
@pageIndex int, -- 输入参数：页码
@pageSize int, -- 输入参数：每页大小（每页多少行）
@rowcount int output, -- 输出参数：总行数
@pagecount int output -- 输出参数：总页数
)
as
begin
-- 1 计算总行数：多表查询（学生表、成绩表）结果的总行数，考虑没有参加考试的学生记录都要查询出来，所以用外连接
select @rowcount = count(*) -- 为总行数设置输出参数结果
from student a left join score b
on a.s_id = b.s_id
-- 2 计算总页数：
if(@rowcount % @pageSize=0)
begin
set @pagecount = @rowcount @pageSize -- 为总页数设置输出参数结果
end
else
begin
set @pagecount = @rowcount @pageSize + 1
end
-- 3 row_number分页查询：使用外连接查询所有的学生记录
select * from
(
select row_number() over(order by a.s_id) as num, -- 行号
a.s_id, -- 学号
s_name, -- 姓名
isnull(c_name,'未考') as c_name, -- 课程名 (null值要处理)
isnull(convert(varchar(10),sc_score),'无') as sc_score -- 成绩 (null值处理要注意将数字的分数结果转字符串输出)
from student a left join score b -- 学生表 与 成绩表 外连接查询，以学生表为主表方向查询所有学生记录
on a.s_id = b.s_id
left join course c -- 接着使用外连接课程表
on b.c_id = c.c_id
) t
where num between ((@pageIndex - 1) * @pageSize+ 1) and (@pageIndex * @pageSize)
end
go
-- 调用过程：sp_scorepage
DECLARE @rowcount int, -- 声明输出参数
@pagecount int
EXEC [dbo].[sp_scorepage]
@pageIndex = 1, -- 页码
@pageSize = 5, -- 每页大小
@rowcount = @rowcount OUTPUT, -- 总行数
@pagecount = @pagecount OUTPUT -- 总页数
SELECT @rowcount as '总行数',
@pagecount as '总页数'
GO
相关文章