|
关于SQL语句分页，网上也有很多，我贴一部分过来，并且总结自己已知的分页到下面，方便日后查阅
1.创建测试环境，(插入100万条数据大概耗时5分钟)。
--创建测试表
create table pagetest
(
id int identity(1,1) not null,col01 int null,col02 nvarchar(50) null,col03 datetime null
)
--1万记录集
declare @i int
set @i=0
while(@i<10000)
begin
insert into pagetest select cast(floor(rand()*10000) as int),left(newid(),10),getdate()
set @i=@i+1
end
2.几种典型的分页sql，下面例子是每页50条，198*50=9900，取第199页数据。
--写法1，not in/top
--写法2，not exists
--写法3，max/top
(select max(id) from (select top 9900 id from pagetest order by id)a)
order by id
--写法4，row_number()
9900
select from
(select row_number()over(order by id)rownumber, from pagetest)a
where rownumber>9900 and rownumber<9951
select from
(select row_number()over(order by id)rownumber, from pagetest)a
where rownumber between 9901 and 9950
--写法5，在csdn上一帖子看到的，row_number() 变体，不基于已有字段产生记录序号，先按条件筛选以及排好序，再在结果集上给一常量列用于产生记录序号
9900
3.分别在1万，10万(取1990页)，100(取19900页）记录集下测试。
测试sql:
select @end_date = getdate()
select datediff(ms,@begin_date,@end_date) as '毫秒'
1万：基本感觉不到差异。
10万：
4.结论：
PS.上面的分页排序都是基于自增字段id。测试环境还提供了int,nvarchar,datetime类型字段，也可以试试。不过对于非主键没索引的大数据量排序效率应该是很不理想的。
5.简单将ROWNUMBER,max/top的方式封装到存储过程。
ALTER PROCEDURE [dbo].[Proc_SqlPageByRownumber]
(
@tbName VARCHAR(255),--表名
@tbGetFields VARCHAR(1000)= '*',--返回字段
@OrderfldName VARCHAR(255),--排序的字段名
@PageSize INT=20,--页尺寸
@PageIndex INT=1,--页码
@OrderType bit = 0,--0升序，非0降序
@strWhere VARCHAR(1000)='',--查询条件
--@TotalCount INT OUTPUT --返回总记录数
)
AS
-- =============================================
-- Author: allen (liyuxin)
-- Create date: 2012-03-30
-- Description: 分页存储过程(支持多表连接查询)
-- Modify [1]: 2012-03-30
-- =============================================
BEGIN
DECLARE @strSql VARCHAR(5000) --主语句
DECLARE @strSqlCount NVARCHAR(500)--查询记录总数主语句
DECLARE @strOrder VARCHAR(300) -- 排序类型
--------------总记录数---------------
IF ISNULL(@strWhere,'') <>''
SET @strSqlCount='Select @TotalCout=count() from ' + @tbName + ' where 1=1 '+ @strWhere
ELSE SET @strSqlCount='Select @TotalCout=count() from ' + @tbName
--exec sp_executesql @strSqlCount,N'@TotalCout int output',@TotalCount output
--------------分页------------
IF @PageIndex <= 0 SET @PageIndex = 1
IF(@OrderType<>0) SET @strOrder=' ORDER BY '+@OrderfldName+' DESC '
ELSE SET @strOrder=' ORDER BY '+@OrderfldName+' ASC '
SET @strSql='SELECT FROM
(SELECT ROW_NUMBER() OVER('+@strOrder+') RowNo,'+ @tbGetFields+' FROM ' + @tbName + ' WHERE 1=1 ' + @strWhere+' ) tb
WHERE tb.RowNo BETWEEN '+str((@PageIndex-1)@PageSize+1)+' AND ' +str(@PageIndex*@PageSize)
exec(@strSql)
SELECT @TotalCount
END
<div class="jb51code">
<pre class="brush:sql;">
public static SqlParameter MakeInParam(string ParamName,SqlDbType DbType,Int32 Size,object Value)
{
return MakeParam(ParamName,DbType,Size,ParameterDirection.Input,Value);
}
public static SqlParameter MakeOutParam(string ParamName,SqlDbType DbType)
{
return MakeParam(ParamName,ParameterDirection.Output,null);
}
public static SqlParameter MakeParam(string ParamName,ParameterDirection Direction,object Value)
{
SqlParameter param;
if (Size > 0)
param = new SqlParameter(ParamName,Size);
else
param = new SqlParameter(ParamName,DbType);
param.Direction = Direction;
if (!(Direction == ParameterDirection.Output && Value == null))
param.Value = Value;
return param;
}
///
/// 分页获取数据列表及总行数
///
/// <param name="tbName">表名
/// <param name="tbGetFields">返回字段
/// <param name="OrderFldName">排序的字段名
/// <param name="PageSize">页尺寸
/// <param name="PageIndex">页码
/// <param name="OrderType">false升序，true降序
/// <param name="strWhere">查询条件
public static DataSet GetPageList(string tbName,string tbGetFields,string OrderFldName,int PageSize,int PageIndex,string strWhere)
{
SqlParameter[] parameters = {
MakeInParam("@tbName",SqlDbType.VarChar,255,tbName),MakeInParam("@tbGetFields",1000,tbGetFields),MakeInParam("@OrderfldName",OrderFldName),MakeInParam("@PageSize",SqlDbType.Int,PageSize),MakeInParam("@PageIndex",PageIndex),MakeInParam("@OrderType",SqlDbType.Bit,OrderType),MakeInParam("@strWhere",strWhere),// MakeOutParam("@TotalCount",SqlDbType.Int)
};
return RunProcedure("Proc_SqlPageByRownumber",parameters,"ds");
}
调用：
1.必填项：tbName，OrderfldName，tbGetFields
2.实例：
Max/top:(简单写了下，需要满足主键字段名称就是"id")
--------------总记录数---------------
if @strWhere !=''
begin
set @strSqlCount='Select @TotalCout=count() from ' + @tbName + ' where '+ @strWhere
end
else
begin
set @strSqlCount='Select @TotalCout=count() from ' + @tbName
end
--------------分页------------
if @PageIndex <= 0
begin
set @PageIndex = 1
end
set @strSql='select top '+str(@PageSize)+' from ' + @tbName + '
where id>(select max(id) from (select top '+str((@PageIndex-1)@PageSize)+' id from ' + @tbName + ''+@strOrder+')a)
'+@strOrder+''
exec sp_executesql @strSqlCount,@Total output
exec(@strSql)
园子里搜到Max/top这么一个版本,看起来很强大，
调用：
以上就是本文针对sql分页查询几种写法的全部内容，希望大家喜欢。 （编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
