Oracle、SQLServer、Access、Mysql数据库分页
来源：互联网 发布：win7连接网络打印机卡 编辑：程序博客网 时间：2024/05/18 14:15
在程序的开发过程中，处理分页是大家接触比较频繁的事件，因为现在软件基本上都是与数据库进行挂钓的。但效率又是我们所追求的，如果是像原来那样把所有满足条件的记录全部都选择出来，再去进行分页处理，那么就会多多的浪费掉许多的系统处理时间。为了能够把效率提高，所以现在我们就只选择我们需要的数据，减少数据库的处理时间，以下就是常用SQL分页处理：
一、SQL Server、Access数据库
这都微软的数据库，都是一家人，基本的操作都是差不多，常采用如下分页语句：
PAGESIZE：每页显示的记录数
CURRENTPAGE：当前页号
数据表的名字是：components
索引主键字是：id
select top PAGESIZE * from components where id not in
(select top (PAGESIZE*(CURRENTPAGE-1))
id from components order by id)order by id
复制代码
如下列：
select top 10 * from components where id not in
(select top 10*10 id from components order by id)
order by id
复制代码
从101条记录开始选择，只选择前面的10条记录
二、Oracle数据库
因为Oracle数据库没有Top关键字，所以这里就不能够像微软的数据据那样操作，这里有两种方法：
(1)、一种是利用相反的。
PAGESIZE：每页显示的记录数
CURRENTPAGE：当前页号
数据表的名字是：components
索引主键字是：id
select * from components where id not
in(select id from components where
rownum<=(PAGESIZE*(CURRENTPAGE-1)))
and rownum<=PAGESIZE order by id;
复制代码
如下例：
select * from components where id not in
(select id from components where rownum<=100)
and rownum<=10 order by id;
复制代码
从101到记录开始选择，选择前面10条。
(2)、使用minus，即中文的意思就是减去。
select * from components where rownum
<=(PAGESIZE*(CURRENTPAGE-1)) minus
select * from components where rownum
<=(PAGESIZE*(CURRENTPAGE-2));
复制代码
如例：
select * from components where
rownum<=10 minus select * from components
where rownum<=5;
复制代码
(3)、一种是利用Oracle的rownum，这个是Oracle查询自动返回的序号，一般不显示，但是可以通过select rownum from [表名]看到，注意，它是从1到当前的记录总数。
select * from (select rownum tid,components.
* from components where rownum<=100) where tid<=10;
复制代码
三、Mysql的分页
通用的情况是这样的：
SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20
复制代码
文中提到一种”clue”的做法，给翻页提供一些”线索”，比如还是SELECT * FROM message ORDER BY id DESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是9527，最小的是9500，如果我们只提供”上一页”、”下一页”这样的跳转（不提供到第N页的跳转），那么在处理”上一页”的时候SQL语句可以是：
SELECT * FROM message WHERE id > 9527 ORDER BY id ASC LIMIT 20;
复制代码
处理”下一页”的时候SQL语句可以是：
SELECT * FROM message WHERE id < 9500 ORDER BY id DESC LIMIT 20;
复制代码
不管翻多少页，每次查询只扫描20行。
我认为这是在原有的分页基础上的一个突破。对于SqlServer和Oracle的分页也很借鉴意义。有条件的可以改写SqlServer和Oracle的翻页Sql，加上Where条件。
如果不加条件限制，可以看到越向后翻，性能是越低的，最后一页就是全表扫描了。如果加上限制，时间几乎是常数了。
分页中，索引也非常重要，在索引列上一定需要建索引，避免全表扫描。
总结起来就几句话：
SqlServer用top，Oracle用rownum，MySql用Limit，排序列建Index，加上Where条件，分页高性能。
六、SQL 2005 版本 通用分页存储过程 不知道哪个在sql2005下的性能是最优的，是第四种呢，还是第五种呢？反正前三种应该不是性能最好的吧。 整理：分页存储过程整理
@tbname sysname, --要分页显示的表名
@FieldKey nvarchar(1000), --用于定位记录的主键(惟一键)字段,可以是逗号分隔的多个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
用于指定排序顺序
@Where nvarchar(1000)='', --查询条件
@PageCount int OUTPUT --总页数
AS
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END
--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
RETURN
END
--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
DECLARE @sql nvarchar(4000)
SET @sql=N'SELECT @PageCount=COUNT(*)'
+N' FROM '+@tbname
+N' '+@Where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END
--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=(@PageCurrent-1)*@PageSize
--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
--处理别名
IF @FieldShow=N'*'
SET @FieldShow=N'a.*'
--生成主键(惟一键)处理条件
DECLARE @Where1 nvarchar(4000),@Where2 nvarchar(4000),
@s nvarchar(1000),@Field sysname
SELECT @Where1=N'',@Where2=N'',@s=@FieldKey
WHILE CHARINDEX(N',',@s)>0
SELECT @Field=LEFT(@s,CHARINDEX(N',',@s)-1),
@s=STUFF(@s,1,CHARINDEX(N',',@s),N''),
@Where1=@Where1+N' AND a.'+@Field+N'=b.'+@Field,
@Where2=@Where2+N' AND b.'+@Field+N' IS NULL',
@Where=REPLACE(@Where,@Field,N'a.'+@Field),
@FieldOrder=REPLACE(@FieldOrder,@Field,N'a.'+@Field),
@FieldShow=REPLACE(@FieldShow,@Field,N'a.'+@Field)
SELECT @Where=REPLACE(@Where,@s,N'a.'+@s),
@FieldOrder=REPLACE(@FieldOrder,@s,N'a.'+@s),
@FieldShow=REPLACE(@FieldShow,@s,N'a.'+@s),
@Where1=STUFF(@Where1+N' AND a.'+@s+N'=b.'+@s,1,5,N''),
@Where2=CASE
WHEN @Where='' THEN N'WHERE ('
ELSE @Where+N' AND ('
END+N'b.'+@s+N' IS NULL'+@Where2+N')'
--执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' a LEFT JOIN(SELECT TOP '+@TopN1
+N' '+@FieldKey
+N' FROM '+@tbname
+N' a '+@Where
+N' '+@FieldOrder
+N')b ON '+@Where1
+N' '+@Where2
+N' '+@FieldOrder)
END
二、字符串缓存实现的通用分页存储过程(转自邹建)
@tbname sysname, --要分页显示的表名
@FieldKey sysname, --用于定位记录的主键(惟一键)字段,只能是单个字段
@PageCurrent int=1, --要显示的页码
@PageSize int=10, --每页的大小(记录数)
@FieldShow nvarchar(1000)='', --以逗号分隔的要显示的字段列表,如果不指定,则显示所有字段
@FieldOrder nvarchar(1000)='', --以逗号分隔的排序字段列表,可以指定在字段后面指定DESC/ASC
用于指定排序顺序
@Where nvarchar(1000)='', --查询条件
@PageCount int OUTPUT --总页数
AS
DECLARE @sql nvarchar(4000)
SET NOCOUNT ON
--检查对象是否有效
IF OBJECT_ID(@tbname) IS NULL
BEGIN
RAISERROR(N'对象"%s"不存在',1,16,@tbname)
RETURN
END
IF OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTable')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsView')=0
AND OBJECTPROPERTY(OBJECT_ID(@tbname),N'IsTableFunction')=0
BEGIN
RAISERROR(N'"%s"不是表、视图或者表值函数',1,16,@tbname)
RETURN
END
--分页字段检查
IF ISNULL(@FieldKey,N'')=''
BEGIN
RAISERROR(N'分页处理需要主键（或者惟一键）',1,16)
RETURN
END
--其他参数检查及规范
IF ISNULL(@PageCurrent,0)<1 SET @PageCurrent=1
IF ISNULL(@PageSize,0)<1 SET @PageSize=10
IF ISNULL(@FieldShow,N'')=N'' SET @FieldShow=N'*'
IF ISNULL(@FieldOrder,N'')=N''
SET @FieldOrder=N''
ELSE
SET @FieldOrder=N'ORDER BY '+LTRIM(@FieldOrder)
IF ISNULL(@Where,N'')=N''
SET @Where=N''
ELSE
SET @Where=N'WHERE ('+@Where+N')'
--如果@PageCount为NULL值,则计算总页数(这样设计可以只在第一次计算总页数,以后调用时,把总页数传回给存储过程,避免再次计算总页数,对于不想计算总页数的处理而言,可以给@PageCount赋值)
IF @PageCount IS NULL
BEGIN
SET @sql=N'SELECT @PageCount=COUNT(*)'
+N' FROM '+@tbname
+N' '+@Where
EXEC sp_executesql @sql,N'@PageCount int OUTPUT',@PageCount OUTPUT
SET @PageCount=(@PageCount+@PageSize-1)/@PageSize
END
--计算分页显示的TOPN值
DECLARE @TopN varchar(20),@TopN1 varchar(20)
SELECT @TopN=@PageSize,
@TopN1=@PageCurrent*@PageSize
--第一页直接显示
IF @PageCurrent=1
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder)
ELSE
BEGIN
SELECT @PageCurrent=@TopN1,
@sql=N'SELECT @n=@n-1,@s=CASE WHEN @n<'+@TopN
+N' THEN @s+N'',''+QUOTENAME(RTRIM(CAST('+@FieldKey
+N' as varchar(8000))),N'''''''') ELSE N'''' END FROM '+@tbname
+N' '+@Where
+N' '+@FieldOrder
SET ROWCOUNT @PageCurrent
EXEC sp_executesql @sql,
N'@n int,@s nvarchar(4000) OUTPUT',
@PageCurrent,@sql OUTPUT
SET ROWCOUNT 0
IF @sql=N''
EXEC(N'SELECT TOP 0'
+N' '+@FieldShow
+N' FROM '+@tbname)
ELSE
BEGIN
SET @sql=STUFF(@sql,1,1,N'')
--执行查询
EXEC(N'SELECT TOP '+@TopN
+N' '+@FieldShow
+N' FROM '+@tbname
+N' WHERE '+@FieldKey
+N' IN('+@sql
+N') '+@FieldOrder)
END
END
三、使用系统存储过程实现的通用分页存储过程(转自邹建)
@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
四、SQL 2005的ROW_NUMBER()实现分页功能
SET @pagenum = 2
SET @pagesize = 3
SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY newsid DESC) AS rownum,
newsid, topic, ntime, hits
FROM news) AS D
WHERE rownum BETWEEN (@pagenum-1)*@pagesize+1 AND @pagenum*@pagesize
ORDER BY newsid DESC
五、使用内存表
(
@pagesize int,
@pagenum int,
@pagecount int output
)
as
begin
--声明变量
declare @tmptable table(id int identity (1,1),userid nchar(5))
declare @idBengin int
declare @idend int
--构造内存表
insert into @tmptable (userid )(select userid from users)
select @pagecount=count(*) from @tmptable
if(@pagecount%@pagesize>0)
set @pagecount=@pagecount/@pagesize+1
else
set @pagecount=@pagecount/@pagesize
set @idBengin=(@pagenum-1)*@pagesize
set @idend=@idBengin+@pagesize
select t2.id,t1.* from users t1,@tmptable t2 where t1.userid=t2.userid and
t2.id>@idBengin and t2.id<=@idend
end
-- Author: 黄剑平
-- Create date: 2007-5-11
-- Description: SQL 2005 版本 通用分页存储过程
-- BLOG: http://www.fnsword.com
-- =============================================
Create PROCEDURE [dbo].[Pagination]
@Page int = 1, -- 当前页码
@PageSize int = 10, -- 每页记录条数(页面大小)
@Table nvarchar(500), -- 表名或视图名，甚至可以是嵌套SQL：(Select * From Tab Where ID>1000) Tab
@Field nvarchar(200) = '*', -- 返回记录集字段名，","隔开，默认是"*"
@OrderBy nvarchar(100) = 'ID ASC', -- 排序规则
@Filter nvarchar(500), -- 过滤条件
@MaxPage smallint output, -- 执行结果 -1 error, 0 false, maxpage true
@TotalRow int output, -- 记录总数 /* 2007-07-12 22:11:00 update */
@Descript varchar(100) output -- 结果描述
AS
BEGIN
Set ROWCOUNT @PageSize;
Set @Descript = 'successful';
-------------------参数检测----------------
IF LEN(RTRIM(LTRIM(@Table))) !> 0
Begin
Set @MaxPage = 0;
Set @Descript = 'table name is empty';
Return;
End
IF LEN(RTRIM(LTRIM(@OrderBy))) !> 0
Begin
Set @MaxPage = 0;
Set @Descript = 'order is empty';
Return;
End
IF ISNULL(@PageSize,0) <= 0
Begin
Set @MaxPage = 0;
Set @Descript = 'page size error';
Return;
End
IF ISNULL(@Page,0) <= 0
Begin
Set @MaxPage = 0;
Set @Descript = 'page error';
Return;
End
-------------------检测结束----------------
Begin Try
-- 整合SQL
Declare @SQL nvarchar(4000), @Portion nvarchar(4000);
Set @Portion = ' ROW_NUMBER() OVER (ORDER BY ' + @OrderBy + ') AS ROWNUM FROM ' + @Table;
Set @Portion = @Portion + (CASE WHEN LEN(@Filter) >= 1 THEN (' Where ' + @Filter + ') AS tab') ELSE (') AS tab') END);
Set @SQL = 'Select TOP(' + CAST(@PageSize AS nvarchar(8)) + ') ' + @Field + ' FROM (Select ' + @Field + ',' + @Portion;
Set @SQL = @SQL + ' Where tab.ROWNUM > ' + CAST((@Page-1)*@PageSize AS nvarchar(8));
-- 执行SQL, 取当前页记录集
Execute(@SQL);
--------------------------------------------------------------------
-- 整合SQL
Set @SQL = 'Set @Rows = (Select MAX(ROWNUM) FROM (Select' + @Portion + ')';
-- 执行SQL, 取最大页码
Execute sp_executesql @SQL, N'@Rows int output', @TotalRow output;
Set @MaxPage = (CASE WHEN (@TotalRow % @PageSize)<>0 THEN (@TotalRow / @PageSize + 1) ELSE (@TotalRow / @PageSize) END);
End Try
Begin Catch
-- 捕捉错误
Set @MaxPage = -1;
Set @Descript = 'error line: ' + CAST(ERROR_LINE() AS varchar(8)) + ', error number: ' + CAST(ERROR_NUMBER() AS varchar(8)) + ', error message: ' + ERROR_MESSAGE();
Return;
End Catch;
-- 执行成功
Return;
END
- Oracle、SQLServer、Access、Mysql数据库分页
- oracle sqlserver mysql数据库分页
- mysql，sqlserver，Access，Oracle 数据库参数化
- Qt连接access、Oracle、MySql、SqlServer数据库
- 数据库分页语句(mysql,oracle,sqlserver,DB2)
- Oracle,SQLServer,MySQL数据库中的分页
- SQLserver/Oracle/MySQL数据库分页 SQL语句
- 数据库分页语句(mysql,oracle,sqlserver,DB2)
- oracle mysql SqlServer 数据库分页实现sql
- 数据库分页语句(mysql,oracle,sqlserver,DB2)
- Oracle、DB2、SQLSERVER、MySQL、Access分页SQL语句
- Oracle、DB2、SQLSERVER、Mysql、Access分页SQL语句梳理
- Oracle、DB2、SQLSERVER、Mysql、Access分页SQL语句梳理
- oracle,sqlServer,MySQL分页
- sqlserver、mysql、oracle分页
- Oracle,mysql,sqlServer分页
- oracle分页-sqlserver分页-mysql分页
- Oracle，MySql，SqlServer等数据库的自增和分页
- 高手要耐得住寂寞
- scrollWidth,clientWidth,offsetWidth的区别
- 我的第一篇文章
- 在虚拟机下如何设置静态IP上网（利用桥接）
- C/C++语言void及void指针深层探索
- Oracle、SQLServer、Access、Mysql数据库分页
- FCKConfig.ProtectedSource是保护而非过滤
- hibernate缓存机制（一）
- Android 的Camera架构介绍
- 本人新手上路，望大家多多关怀哦！
- JSON技术入门
- new
- JavaScript高级培训－自定义对象
- html中相关解释
