.NET学习爱好者

热爱编程事业

  博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

最近在网上看到许多数据库分页的原理,下面就链出几个网址:
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 )

posted on 2007-01-14 09:48  风景  阅读(546)  评论(1编辑  收藏  举报