本篇文章为大家展示了sql中怎么实现分页查询，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。

1.创建测试环境，(插入100万条数据大概耗时5分钟)。

createdatabaseDBTestuseDBTest--创建测试表createtablepagetest(idintidentity(1,1)notnull,col01intnull,col02nvarchar(50)null,col03datetimenull)--1万记录集declare@iintset@i=0while(@i<10000)begininsertintopagetestselectcast(floor(rand()*10000)asint),left(newid(),10),getdate()set@i=@i+1end

2.几种典型的分页sql，下面例子是每页50条，198*50=9900，取第199页数据。

--写法1，not in/top

selecttop50*frompagetestwhereidnotin(selecttop9900idfrompagetestorderbyid)orderbyid

--写法2，not exists

selecttop50*frompagetestwherenotexists(select1from(selecttop9900idfrompagetestorderbyid)awherea.id=pagetest.id)orderbyid

--写法3，max/top

selecttop50*frompagetestwhereid>(selectmax(id)from(selecttop9900idfrompagetestorderbyid)a)orderbyid

--写法4，row_number()

selecttop50*from(selectrow_number()over(orderbyid)rownumber,*frompagetest)awhererownumber>9900select*from(selectrow_number()over(orderbyid)rownumber,*frompagetest)awhererownumber>9900andrownumber<9951select*from(selectrow_number()over(orderbyid)rownumber,*frompagetest)awhererownumberbetween9901and9950

--写法5，在csdn上一帖子看到的，row_number() 变体，不基于已有字段产生记录序号，先按条件筛选以及排好序，再在结果集上给一常量列用于产生记录序号

select*from(selectrow_number()over(orderbytempColumn)rownumber,*from(selecttop9950tempColumn=0,*frompagetestwhere1=1orderbyid)a)bwhererownumber>9900

3.分别在1万，10万(取1990页)，100(取19900页）记录集下测试。

测试sql:

declare@begin_datedatetimedeclare@end_datedatetimeselect@begin_date=getdate()<.....YOURCODE.....>select@end_date=getdate()selectdatediff(ms,@begin_date,@end_date)as'毫秒'

1万：基本感觉不到差异。

10万：

4.结论：

1.max/top,ROW_NUMBER()都是比较不错的分页方法。相比ROW_NUMBER()只支持sql2005及以上版本，max/top有更好的可移植性，能同时适用于sql2000,access。

2.not exists感觉是要比not in效率高一点点。

3.ROW_NUMBER()的3种不同写法效率看起来差不多。

4.ROW_NUMBER() 的变体基于我这个测试效率实在不好。原帖在这里 http://topic.csdn.net/u/20100617/04/80d1bd99-2e1c-4083-ad87-72bf706cb536.html

PS.上面的分页排序都是基于自增字段id。测试环境还提供了int,nvarchar,datetime类型字段，也可以试试。不过对于非主键没索引的大数据量排序效率应该是很不理想的。

5.简单将ROWNUMBER,max/top的方式封装到存储过程。

ROWNUMBER():ALTERPROCEDURE[dbo].[Proc_SqlPageByRownumber](@tbNameVARCHAR(255),--表名@tbGetFieldsVARCHAR(1000)='*',--返回字段@OrderfldNameVARCHAR(255),--排序的字段名@PageSizeINT=20,--页尺寸@PageIndexINT=1,--页码@OrderTypebit=0,--0升序，非0降序@strWhereVARCHAR(1000)='',--查询条件--@TotalCountINTOUTPUT--返回总记录数)AS--=============================================--Author:allen(liyuxin)--Createdate:2012-03-30--Description:分页存储过程(支持多表连接查询)--Modify[1]:2012-03-30--=============================================BEGINDECLARE@strSqlVARCHAR(5000)--主语句DECLARE@strSqlCountNVARCHAR(500)--查询记录总数主语句DECLARE@strOrderVARCHAR(300)--排序类型--------------总记录数---------------IFISNULL(@strWhere,'')<>''SET@strSqlCount='Select@TotalCout=count(*)from'+@tbName+'where1=1'+@strWhereELSESET@strSqlCount='Select@TotalCout=count(*)from'+@tbName--execsp_executesql@strSqlCount,N'@TotalCoutintoutput',@TotalCountoutput--------------分页------------IF@PageIndex<=0SET@PageIndex=1IF(@OrderType<>0)SET@strOrder='ORDERBY'+@OrderfldName+'DESC'ELSESET@strOrder='ORDERBY'+@OrderfldName+'ASC'SET@strSql='SELECT*FROM(SELECTROW_NUMBER()OVER('+@strOrder+')RowNo,'+@tbGetFields+'FROM'+@tbName+'WHERE1=1'+@strWhere+')tbWHEREtb.RowNoBETWEEN'+str((@PageIndex-1)*@PageSize+1)+'AND'+str(@PageIndex*@PageSize)exec(@strSql)SELECT@TotalCountEND

publicstaticSqlParameterMakeInParam(stringParamName,SqlDbTypeDbType,Int32Size,objectValue){returnMakeParam(ParamName,DbType,Size,ParameterDirection.Input,Value);}publicstaticSqlParameterMakeOutParam(stringParamName,SqlDbTypeDbType){returnMakeParam(ParamName,DbType,0,ParameterDirection.Output,null);}publicstaticSqlParameterMakeParam(stringParamName,SqlDbTypeDbType,Int32Size,ParameterDirectionDirection,objectValue){SqlParameterparam;if(Size>0)param=newSqlParameter(ParamName,DbType,Size);elseparam=newSqlParameter(ParamName,DbType);param.Direction=Direction;if(!(Direction==ParameterDirection.Output&&Value==null))param.Value=Value;returnparam;}///<summary>///分页获取数据列表及总行数///</summary>///<paramname="tbName">表名</param>///<paramname="tbGetFields">返回字段</param>///<paramname="OrderFldName">排序的字段名</param>///<paramname="PageSize">页尺寸</param>///<paramname="PageIndex">页码</param>///<paramname="OrderType">false升序，true降序</param>///<paramname="strWhere">查询条件</param>publicstaticDataSetGetPageList(stringtbName,stringtbGetFields,stringOrderFldName,intPageSize,intPageIndex,stringstrWhere){SqlParameter[]parameters={MakeInParam("@tbName",SqlDbType.VarChar,255,tbName),MakeInParam("@tbGetFields",SqlDbType.VarChar,1000,tbGetFields),MakeInParam("@OrderfldName",SqlDbType.VarChar,255,OrderFldName),MakeInParam("@PageSize",SqlDbType.Int,0,PageSize),MakeInParam("@PageIndex",SqlDbType.Int,0,PageIndex),MakeInParam("@OrderType",SqlDbType.Bit,0,OrderType),MakeInParam("@strWhere",SqlDbType.VarChar,1000,strWhere),//MakeOutParam("@TotalCount",SqlDbType.Int)};returnRunProcedure("Proc_SqlPageByRownumber",parameters,"ds");}

调用：

publicDataTableGetList(stringtbName,stringtbGetFields,stringOrderFldName,intPageSize,intPageIndex,stringstrWhere,refintTotalCount){DataSetds=dal.GetList(tbName,tbGetFields,OrderFldName,PageSize,PageIndex,strWhere);TotalCount=Convert.ToInt32(ds.Tables[1].Rows[0][0]);returnds.Tables[0];}

注意：多表连接时需注意的地方

1.必填项：tbName，OrderfldName，tbGetFields

2.实例：

tbName=“UserInfouINNERJOINDepartmentdONu.DepID=d.ID”tbGetFields=“u.IDASUserID,u.Name,u.Sex,d.IDASDepID,d.DefName”OrderfldName=“u.ID,ASC|u.Name,DESC”(格式：Name,ASC|ID,DESC)strWhere：每个条件前必须添加AND(例如：ANDUserInfo.DepID=1)

Max/top:(简单写了下，需要满足主键字段名称就是"id")

createproc[dbo].[spSqlPageByMaxTop]@tbNamevarchar(255),--表名@tbFieldsvarchar(1000),--返回字段@PageSizeint,--页尺寸@PageIndexint,--页码@strWherevarchar(1000),--查询条件@StrOrdervarchar(255),--排序条件@Totalintoutput--返回总记录数asdeclare@strSqlvarchar(5000)--主语句declare@strSqlCountnvarchar(500)--查询记录总数主语句--------------总记录数---------------if@strWhere!=''beginset@strSqlCount='Select@TotalCout=count(*)from'+@tbName+'where'+@strWhereendelsebeginset@strSqlCount='Select@TotalCout=count(*)from'+@tbNameend--------------分页------------if@PageIndex<=0beginset@PageIndex=1endset@strSql='selecttop'+str(@PageSize)+'*from'+@tbName+'whereid>(selectmax(id)from(selecttop'+str((@PageIndex-1)*@PageSize)+'idfrom'+@tbName+''+@strOrder+')a)'+@strOrder+''execsp_executesql@strSqlCount,N'@TotalCoutintoutput',@Totaloutputexec(@strSql)

调用：

declare@countint--exec[dbo].[spSqlPageByRownumber]'pagetest','*',50,20,'','orderbyidasc',@countoutputexec[dbo].[spSqlPageByMaxTop]'pagetest','*',50,20,'','orderbyidasc',@countoutputselect@count

上述内容就是sql中怎么实现分页查询，你们学到知识或技能了吗？如果还想学到更多技能或者丰富自己的知识储备，欢迎关注亿速云行业资讯频道。