时间:2021-07-01 10:21:17 帮助过:34人阅读
sqlserver2005使用row_number() over分页的实现方法,需要的朋友可以参考下。
语法:ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)
- <br>select * from ( <br> select *, ROW_NUMBER() OVER(Order by a.CreateTime DESC ) AS RowNumber from table_name as a <br> ) as b <br> where RowNumber BETWEEN 1 and 5 <br> <br>将会返回table表 <br><br>其中有一列名字为 RowNumber, 编号从1开始 <br><br>示例: <br>xlh row_num <br>1700 1 <br>1500 2 <br>1085 3 <br>710 4 <br><br>有了row_num 编号之后是不是很方便分页呀! 哈哈 <br><br>只要使用 <br>where RowNumber between <br>就可以实现分页了 呵呵(从此分页就是这么简单) <br><br>例子: <br> 代码如下:<pre class="brush:php;toolbar:false layui-box layui-code-view layui-code-notepad"><ol class="layui-code-ol"><li><br>select * <br>from( <br>select ROW_NUMBER() OVER( ORDER BY PSIO.CreateTime DESC ) AS RowNumber,PSIO.SeqNo,PSIO.CreateTime from dbo.Output PSIO <br>inner join Album PPA on PSIO.PPAID=PPA.PPAID <br>where PPA.PPAID=103--PPAID=3.PPAID <br>) T where RowNumber BETWEEN 1 and 5 order by 1 <br> <br>在当前select里面不能采用 RowNumber字段,并且不能使用排序<br><br>方式一 <br>select top @pageSize * from company where id not in <br>(select top @pageSize*(@pageIndex-1) id from company) <br><br>方式二ROW_NUMBER()OVER <br><br>--ROW_NUMBER() 就是生成一个有顺序的行号,而他生成顺序的标准,就是后面紧跟的OVER(ORDER BY ID) <br>--还必须添加OVER语句以便告诉SQL Server你希望怎样添加行序号。 <br>select getdate() <br>select * from company where id in ( <br>--搜索出settable表中所有的编号,也就是company表中的id,这里只不过要得到num(即有顺序的编号) <br>select id from <br>--搜索出出表中的所有的id,并且新建一列num用来存取排序的编号,并且把这张表赋值给settable <br>(select id,row_number() over (order by id) as <br>num from company) <br>as settable <br>--添加搜索条件页索引和页大小 <br>where num between (@pageIndex-1)*@pageSize+1 and @pageIndex*@pageSize) <br>select getdate() <br><br>方式三 <br>SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY id asc) AS rownum, <br>id <br>FROM company ) AS D <br>WHERE rownum BETWEEN (@pageIndex-1)*@pageSize+1 AND @pageIndex*@pageSize <br>ORDER BY id asc <br><br>Sql Server 2000的自定义分页,但是在sql server 2000中,要实现显示某一页,就返回那一页数据的效果的方法实在不尽人意.网上很多通用的分页存储过程,但看着就头大.如果使用我前面提到的使用in,not in,top来进行返回特定页,特殊的限制又会比较多(比如ID要递增).现在Sql Server 2005中提供了一个函数ROW_NUMBER(),可以使自定义分页变得简单许多. <br>我们先来看看ROW_NUMBER()是干什么的.执行下面这段SQL语句: <br>SELECT [ReportID],[UserName], [ReportID], <br>[TimeStart], [TimeEnd],ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo <br>FROM [ExecutionLog] <br>很简单,ROW_NUMBER() 就是生成一个顺序的行号,而他生成顺序的标准,就是后面紧跟的OVER(ORDER BY ReportID).现在,你看到了自定义分页的影子了吗?:)下面,我们看看怎么具体应用这个RowNo进行分页. <br>现在,假设我每一页的数据是10条,我们就可以使用如下所示的SQL语句返回指定页的数据: <br>@" <br>SELECT TOP 10 * <br>FROM <br>( <br>SELECT top 10 [InstanceName], [UserName], [ReportID], <br>[TimeStart], [TimeEnd],ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo <br>FROM [ExecutionLog] <br>) AS A <br>WHERE RowNo > " + pageIndex*10 <br>pageIndex就是我们需要数据的页数.很简单,不是吗?并且,这种方式几乎没有什么限制,因为他相当于对于任何检索,都生成了一个新的排序列.我们就可以使用该列进行自定义分页. <br>================ <br>下面举个例子: <br>ROW_NUMBER函数 <br> SQL Server2005为我们引入了一个ROW_NUMBER函数。你是否曾经需要为你的查询结果集做行序号?你有时会发现能够为行做序号是一件很有用的事情。从前,你不得不作棘手的事,像创建一个有序号列的临时表,然后把你的SELECT结果插入到这个临时表中。现在,用ROW_NUMBER函数,你就可以获得添加在你的结果集的增加列中的行序号。为了获得行序号,你只要简单的将ROW_NUMBER函数作为一列添加进你的SELECT语句中。你还必须添加OVER语句以便告诉SQL Server你希望怎样添加行序号。 <br> SELECT ROW_NUMBER() OVER(ORDER BY employee_id) AS 'Row Number', * from <br> dbo.employee <br><br>结果 <br>Row Number employee_id Firstname Lastname soc_sec <br>1 5623222 Tim Jones 123-65-8745 <br>2 5632111 Rob Kinkad 456-69-8754 <br>3 6365666 Jim Miller 236-56-8989 <br>4 7563333 Joe Roberts 564-89-5555 <br><br> 这个查询返回所有的雇员和一个显示每条记录在哪一行的一个序号。OVER语句使SQL Server基于employee_id列增加行序号。换句话说,产生了行序号,就好像数据按employee_id做了排序。这是很重要的一点,因为你仍然可以改变SELECT的排序顺序。以下面的查询为例: <br> SELECT ROW_NUMBER() OVER(ORDER BY employee_id) AS 'Row Number', * from <br> dbo.employee <br> ORDER BY soc_sec <br><br>结果 <br>Row Number employee_id Firstname Lastname soc_sec <br>1 5623222 Tim Jones 123-65-8745 <br>3 6365666 Jim Miller 236-56-8989 <br>2 5632111 Rob Kinkad 456-69-8754 <br>4 7563333 Joe Roberts 564-89-5555 <br><br> 注意第二个结果集数据是按社会安全编号来排序的,但是行号仍然创建得好像数据是按employee_id排序的。 <br>-------------------------------分页存储过程 <br>IF EXISTS (SELECT * FROM sysobjects where name='P_student') <br>DROP PROCEDURE P_student <br>go <br>CREATE PROCEDURE P_student <br>@startIndex INT, <br>@pageSize INT <br>AS <br>begin WITH studentList AS ( <br>SELECT ROW_NUMBER() OVER (ORDER BY O.stuid ) Row, <br>O.stuid,O.stuname,O.stuage,O.stuinfo <br>from student O) <br>SELECT Row, stuid,stuname,stuage,stuinfo <br>FROM studentList <br>WHERE Row between @startIndex and @startIndex+@pageSize-1 <br>end <br><br>-------------------分页2--------- <br>set ANSI_NULLS ON <br>set QUOTED_IDENTIFIER ON <br>GO <br>ALTER PROCEDURE [dbo].[Deer_Page] <br>( <br>@startIndex INT <br>,@pageSize INT <br>,@strSql varchar(5000) ---查询条件 <br>,@TableName varchar(50) <br>,@DoCount AS bit=1 -- 0值返回记录总数, 非 0 值则返回记录 <br>) <br>AS <br>begin tran <br>IF @DoCount=0 <br>Goto GetCount <br>Else <br>Goto GetSearch <br>GetCount: --返回记录总数 <br>DECLARE @SearchSql AS Nvarchar(4000) <br>SET @SearchSql= 'SELECT Count(*) AS Total FROM '+@TableName+' WHERE IntReserve1=0' <br>exec sp_executesql @SearchSql <br>--print @SearchSql <br>COMMIT TRAN <br>return <br>GetSearch: --返回记录 <br>DECLARE @SqlQuery varchar(4000) <br>SET @SqlQuery='SELECT * FROM <br>(SELECT ROW_NUMBER() OVER (ORDER BY O.ID ) Row, * from '+@TableName+' O Where IntReserve1=0) as temp <br>WHERE Row BETWEEN '+cast(@startIndex as varchar) +' and '+cast(@startIndex+@pageSize-1 as varchar)+ @strsql <br>---print @SqlQuery <br>execute(@SqlQuery) <br>COMMIT TRAN </li></ol></pre>