sqlserver 分页的方法

最新推荐文章于 2024-09-14 19:04:00 发布

原创最新推荐文章于 2024-09-14 19:04:00 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

SQLSERVER 专栏收录该内容

16 篇文章

订阅专栏

本文介绍了在 SQL Server 2000 和 2005 中实现分页查询的不同方法，包括使用 NOT IN 子句和 ROW_NUMBER() 函数进行高效数据分页的技术细节。

sqlserver2000时的分页思路
1.分页查询时，首先将数据排序
   select * from MyStudent order by fid desc
2.取第一页数据
   select Top 5 * from MyStudent order by fid desc
3.取第二页数据
   select Top 5 * from MyStudent
   where fid not in
   (
       select Top 5 * from MyStudent order by fid desc
   )
   order by fid desc
4.取第三页的数据
   select Top 5 * from MyStudent
   where fid not in
   (
       select Top (2*5) * from MyStudent order by fid desc
   )
   order by fid desc
4.取第四页的数据
   select Top 5 * from MyStudent
   where fid not in
   (
       select Top (3*5) * from MyStudent order by fid desc
   )
   order by fid desc

sqlserver2005时的分页思路

--第7页数据
select * from
(
   select *,row_number() over(order by fid desc) as runumber
   from MyStudent
) as tbl
where tbl.rnumber between 6*5+1 and 7*5

--第20页数据
select * from
(
   select *,row_number() over(order by fid desc) as runumber
   from MyStudent
) as tbl
where tbl.rnumber between 19*5+1 and 20 *5

--开窗函数与聚合函数一起使用，可以让聚合函数对每一条数据都计算一次
select *,count(*) over() as 总条数 from MyStudent