SQLServer分页查询

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)

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