最近在网上看到许多数据库分页的原理,下面就链出几个网址:
http://blog.tom.com/blog/read.php?bloggerid=273059&blogid=42423
http://dev.csdn.net/article/33/33707.shtm
http://blog.csdn.net/lonz/archive/2004/11/12/178945.aspx
http://www.sky-cn.cn/webmaster/Database/ot/YesDown_Edu_3629.html
http://www.cnblogs.com/cherish58/archive/2007/05/08/739465.html(经典)
--建立表：
CREATE TABLE [UserInfo] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[FirstName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[LastName] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[Country] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Note] [nvarchar] (2000) COLLATE Chinese_PRC_CI_AS NULL
)
GO
--插入测试数据：(5万条)
SET IDENTITY_INSERT UserInfo ON
declare @i int
set @i=1
while @i<=50000
begin
insert into UserInfo([id], FirstName, LastName, Country,Note) values(@i,'FirstName_' + Convert(varchar,@i),'LastName_XXX' + Convert(varchar,@i),'Country_XXX' + Convert(varchar,@i),'Note_XXX' + Convert(varchar,@i))
set @i=@i+1
end
SET IDENTITY_INSERT TestTable OFF
-------------------------------------
--分页方案一：(利用Not In和SELECT TOP分页)
--语句形式：
/*
SELECT TOP 页大小 *
FROM TableName
WHERE ID NOT IN (
SELECT TOP 页大小*页数 id FROM 表 ORDER BY id)
)
ORDER BY ID
*/
SELECT TOP 10 *
FROM UserInfo
WHERE ID NOT IN (
SELECT TOP 20 id FROM UserInfo ORDER BY id)
ORDER BY ID
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc proc_pageF
@currentPage int,
@pageSize int= 10
as
declare @sql varchar(8000)
declare @size varchar(10)
declare @curPage varchar(10)
set @size = convert(varchar,@pageSize)
set @curPage=convert(varchar,@currentPage)
set @sql = 'SELECT TOP ' + @size + ' * FROM UserInfo '
+ 'WHERE ID NOT IN ( SELECT TOP '
+ convert(varchar,@pageSize*(@currentPage-1))
+ ' id FROM UserInfo ORDER BY id) ORDER BY ID '
exec(@sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-------------------------------------
--分页方案二：(利用ID大于多少和SELECT TOP分页）
--语句形式：
/*
SELECT TOP 页大小 *
FROM TableName
WHERE ID > (
SELECT MAX(id) FROM (SELECT TOP 页大小*页数 id FROM TableName ORDER BY id) AS T
)
ORDER BY ID
*/
SELECT TOP 10 *
FROM UserInfo
WHERE ID > (
SELECT MAX(id) FROM (SELECT TOP 20 id FROM UserInfo ORDER BY id) AS T
)
ORDER BY ID
-------------------------------------
--分页方案三：
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
alter PROCEDURE [DBO].[proc_PageTable] (
@Note VARCHAR,
@CurrentPage INT,
@PageSize INT,
@TotalRecords INT OUTPUT
)
AS
SET NOCOUNT ON
DECLARE @TEMP TABLE (
[rowID] INT IDENTITY(1, 1) PRIMARY KEY,
[selID] INT
)
SET @Note = '%' + @Note + '%'
--总记录数
SELECT @TotalRecords=count([ID]) FROM [DBO].[UserInfo] WHERE [NOTE] like @Note
DECLARE @FirstRecord INT
DECLARE @LastRecord INT
SELECT @FirstRecord = @PageSize * (@CurrentPage - 1) + 1
SELECT @LastRecord = @FirstRecord + @PageSize - 1
--SET ROWCOUNT 使 SQL Server 在返回指定的行数之后停止处理查询。
SET ROWCOUNT @LastRecord
INSERT INTO @TEMP (
[selID]
)
SELECT [ID] FROM [DBO].[UserInfo] WHERE [NOTE] like @Note
ORDER BY [ID] DESC
SET ROWCOUNT 0
SELECT
[ID],
[FirstName],
[LastName],
[Country],
[Note]
FROM
@TEMP AS T JOIN [DBO].[UserInfo] AS U
ON
T.[selID] = U.[ID]
WHERE
T.[rowID] >= @FirstRecord
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_PageView]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_PageView]
GO
-------------------------------------
--分页方案三：(利用SQL的游标存储过程分页)微软不公开的存储过程的分页
/*
--利用SQL未公开的存储过程实现分页
方法简单且效率高,已知的问题就是要多返回一个空的记录集
解决的方法是在前台调用时,用 set recordset=recordset.nextrecordset
的方法跳过第一个记录集
此方法由J9988提供,改成了方便调用的存储过程
--邹建 2004.05(引用请保留此信息)--*/
/*--调用示例
declare @PageCount int
exec sp_PageView
@sql='select * from sysobjects',
@PageCurrent=2,
@PageCount=@PageCount out
SELECT @PageCount
--*/
CREATE PROC sp_PageView
@sql ntext, --要执行的sql语句
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小
@PageCount int OUTPUT --总页数
AS
SET NOCOUNT ON
DECLARE @p1 int
--初始化分页游标
EXEC sp_cursoropen
@cursor=@p1 OUTPUT,
@stmt=@sql,
@scrollopt=1,
@ccopt=1,
@rowcount=@PageCount OUTPUT
--计算总页数
IF ISNULL(@PageSize,0)<1
SET @PageSize=10
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
IF ISNULL(@PageCurrent,0)<1 OR ISNULL(@PageCurrent,0)>@PageCount
SET @PageCurrent=1
ELSE
SET @PageCurrent=(@PageCurrent-1)*@PageSize+1
--显示指定页的数据
EXEC sp_cursorfetch @p1,16,@PageCurrent,@PageSize
--关闭分页游标
EXEC sp_cursorclose @p1
GO
指定返回第几条
select * from movie
where
id in(select top 4 id from movie )
and
id not in(select top 3 id from movie )