sqlserver2005使用row_number() over分页的实现方法分享

发布时间：2023-08-05 14:46:40 所属栏目：MsSql教程来源：

导读：语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

例子:

<div class="codetitle"><a style="CURSOR: pointer" data="15516" class="copybut" id="copybut15516" onclick="doCopy(&#39

语法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN)

例子:

<div class="codetitle"><a style="CURSOR: pointer" data="15516" class="copybut" id="copybut15516" onclick="doCopy('code15516')"> 代码如下:

<div class="codebody" id="code15516">

select from (

　　　　select ,ROW_NUMBER() OVER(Order by a.CreateTime DESC ) AS RowNumber from table_name as a

　　) as b

　　where RowNumber BETWEEN 1 and 5

将会返回table表其中有一列名字为 RowNumber,编号从1开始示例：

xlh row_num

1700 1

1500 2

1085 3

710 4 有了row_num 编号之后是不是很方便分页呀! 哈哈只要使用

where RowNumber between

就可以实现分页了呵呵(从此分页就是这么简单) 例子:

<div class="codetitle"><a style="CURSOR: pointer" data="7230" class="copybut" id="copybut7230" onclick="doCopy('code7230')"> 代码如下:

<div class="codebody" id="code7230">

select

from(

select ROW_NUMBER() OVER( ORDER BY PSIO.CreateTime DESC ) AS RowNumber,PSIO.SeqNo,PSIO.CreateTime from dbo.Output PSIO

inner join Album PPA on PSIO.PPAID=PPA.PPAID

where PPA.PPAID=103--PPAID=3.PPAID

) T where RowNumber BETWEEN 1 and 5 order by 1

在当前select里面不能采用 RowNumber字段,并且不能使用排序方式一

select top @pageSize from company where id not in

(select top @pageSize(@pageIndex-1) id from company) 方式二ROW_NUMBER()OVER --ROW_NUMBER() 就是生成一个有顺序的行号,而他生成顺序的标准,就是后面紧跟的OVER(ORDER BY ID)

--还必须添加OVER语句以便告诉sql Server你希望怎样添加行序号。

select getdate()

select from company where id in (

--搜索出settable表中所有的编号，也就是company表中的id，这里只不过要得到num（即有顺序的编号）

select id from

--搜索出出表中的所有的id，并且新建一列num用来存取排序的编号，并且把这张表赋值给settable

(select id,row_number() over (order by id) as

num from company)

as settable

--添加搜索条件页索引和页大小

where num between (@pageIndex-1)@pageSize+1 and @pageIndex@pageSize)

select getdate() 方式三

SELECT FROM (SELECT ROW_NUMBER() OVER(ORDER BY id asc) AS rownum,

id

FROM company ) AS D

WHERE rownum BETWEEN (@pageIndex-1)@pageSize+1 AND @pageIndex@pageSize

ORDER BY id asc sql Server 2000的自定义分页,但是在sql server 2000中,要实现显示某一页,就返回那一页数据的效果的方法实在不尽人意.网上很多通用的分页存储过程,但看着就头大.如果使用我前面提到的使用in,not in,top来进行返回特定页,特殊的限制又会比较多(比如ID要递增).现在sql Server 2005中提供了一个函数ROW_NUMBER(),可以使自定义分页变得简单许多.

我们先来看看ROW_NUMBER()是干什么的.执行下面这段sql语句:

SELECT [ReportID],[UserName],[ReportID],

[TimeStart],[TimeEnd],ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo

FROM [ExecutionLog]

很简单,ROW_NUMBER() 就是生成一个顺序的行号,就是后面紧跟的OVER(ORDER BY ReportID).现在,你看到了自定义分页的影子了吗?:)下面,我们看看怎么具体应用这个RowNo进行分页.

现在,假设我每一页的数据是10条,我们就可以使用如下所示的sql语句返回指定页的数据:

@"

SELECT TOP 10

FROM

(

SELECT top 10 [InstanceName],ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo

FROM [ExecutionLog]

) AS A

WHERE RowNo > " + pageIndex10

pageIndex就是我们需要数据的页数.很简单,不是吗?并且,这种方式几乎没有什么限制,因为他相当于对于任何检索,都生成了一个新的排序列.我们就可以使用该列进行自定义分页.

================

下面举个例子:

ROW_NUMBER函数

　　sql Server2005为我们引入了一个ROW_NUMBER函数。你是否曾经需要为你的查询结果集做行序号?你有时会发现能够为行做序号是一件很有用的事情。从前，你不得不作棘手的事，像创建一个有序号列的临时表，然后把你的SELECT结果插入到这个临时表中。现在，用ROW_NUMBER函数，你就可以获得添加在你的结果集的增加列中的行序号。为了获得行序号，你只要简单的将ROW_NUMBER函数作为一列添加进你的SELECT语句中。你还必须添加OVER语句以便告诉sql Server你希望怎样添加行序号。

　　SELECT ROW_NUMBER() OVER(ORDER BY employee_id) AS 'Row Number', from

　　dbo.employee 结果

Row Number employee_id Firstname Lastname soc_sec

1 5623222 Tim Jones 123-65-8745

2 5632111 Rob Kinkad 456-69-8754

3 6365666 Jim Miller 236-56-8989

4 7563333 Joe Roberts 564-89-5555 　　这个查询返回所有的雇员和一个显示每条记录在哪一行的一个序号。OVER语句使sql Server基于employee_id列增加行序号。换句话说，产生了行序号，就好像数据按employee_id做了排序。这是很重要的一点，因为你仍然可以改变SELECT的排序顺序。以下面的查询为例：

　　SELECT ROW_NUMBER() OVER(ORDER BY employee_id) AS 'Row Number', from

　　dbo.employee

　　ORDER BY soc_sec 结果

Row Number employee_id Firstname Lastname soc_sec

1 5623222 Tim Jones 123-65-8745

3 6365666 Jim Miller 236-56-8989

2 5632111 Rob Kinkad 456-69-8754

4 7563333 Joe Roberts 564-89-5555 　　注意第二个结果集数据是按社会安全编号来排序的，但是行号仍然创建得好像数据是按employee_id排序的。

-------------------------------分页存储过程

IF EXISTS (SELECT FROM sysobjects where name='P_student')

DROP PROCEDURE P_student

go

CREATE PROCEDURE P_student

@startIndex INT,

@pageSize INT

AS

begin WITH studentList AS (

SELECT ROW_NUMBER() OVER (ORDER BY O.stuid ) Row,

O.stuid,O.stuname,O.stuage,O.stuinfo

from student O)

SELECT Row,stuid,stuname,stuage,stuinfo

FROM studentList

WHERE Row between @startIndex and @startIndex+@pageSize-1

end -------------------分页2---------

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[Deer_Page]

(

@startIndex INT

,@pageSize INT

,@strsql varchar(5000) ---查询条件

,@TableName varchar(50)

,@DoCount AS bit=1 -- 0值返回记录总数,非 0 值则返回记录

)

AS

begin tran

IF @DoCount=0

Goto GetCount

Else

Goto GetSearch

GetCount: --返回记录总数

DECLARE @Searchsql AS Nvarchar(4000)

SET @Searchsql= 'SELECT Count() AS Total FROM '+@TableName+' WHERE IntReserve1=0'

exec sp_executesql @Searchsql

--print @Searchsql

COMMIT TRAN

return

GetSearch: --返回记录

DECLARE @sqlQuery varchar(4000)

SET @sqlQuery='SELECT FROM

(SELECT ROW_NUMBER() OVER (ORDER BY O.ID ) Row,* from '+@TableName+' O Where IntReserve1=0) as temp

WHERE Row BETWEEN '+cast(@startIndex as varchar) +' and '+cast(@startIndex+@pageSize-1 as varchar)+ @strsql

---print @sqlQuery

execute(@sqlQuery)

COMMIT TRAN

（编辑：聊城站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!