分页

最新推荐文章于 2025-10-21 22:09:11 发布

原创最新推荐文章于 2025-10-21 22:09:11 发布 · 365 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#table #null #database #存储

BySelf 专栏收录该内容

4 篇文章

订阅专栏

本文详细介绍了如何使用SQL实现分页查询，并通过存储过程进一步优化查询效率。主要内容包括分页查询的基本语法、使用TOP/MAX模式、ROW_NUMBER()函数、排除已展示数据等方法，并提供了实现步骤与示例。

create database DBPage
use DBpage

create table pagetable
(
id int identity(1,1) not null,
col1 int null,
col2 nvarchar(50) null,
col3 datetime null
)

declare @i int
set @i=0
while(@i<100)
begin
insert into pagetable select cast(floor(rand()*100) as int),left(newid(),10),getdate()
set @i=@i+1
end

select * from pagetable

--每页显示十条。看第九页数据 10*8=80 pagesize*pageindex

--top
--1.select top pagesize *from table where id not in (select top pagesize*pageindex id from table)
--pageindex+1为页数，pagesize为每页显示的条数
select top 10 *from pagetable where id not in
(select top 80 id from pagetable)

select top 50 * from pagetest where id not in (select top 9900 id from pagetest order by id)order by id

--row_number()
--2.select * from (select row_number() over(order by id) num,* from table) a
--where a.num>pagesize*pageindex and a.num<pagesize*(pageindex+1)+1
select * from
(select row_number() over(order by id) rownum,* from pagetable) a
where a.rownum>80 and a.rownum<91

--top/max模式
--3.select top pagesize * from table where id>
--(select max(id) from (select top pagesize*pageindex id from table order by id)a) order by id
select top 10 * from pagetable where id>
(select max(id) from (select top 80 id from pagetable order by id)a)
order by id

--4.
select top 10 * from pagetable where not exists
(select 1 from (select top 80 id from pagetable order by id)a
where a.id=pagetable.id)order by id

另外这里把分页写成的存储过程，功能很强大

http://www.cnblogs.com/hertcloud/archive/2005/09/29/246702.html