語法：ROW_NUMBER() OVER(PARTITION BY COLUMN ORDER BY COLUMN) <BR>
例子:

將會(huì)返回table表

其中有一列名字為 RowNumber, 編號(hào)從1開始

示例：
xlh row_num
1700 1
1500 2
1085 3
710 4

有了row_num 編號(hào)之后是不是很方便分頁呀! 哈哈

只要使用
where RowNumber between
就可以實(shí)現(xiàn)分頁了 呵呵(從此分頁就是這么簡(jiǎn)單)

例子:

在當(dāng)前select里面不能采用 RowNumber字段,并且不能使用排序

方式一
select top @pageSize * from company where id not in
(select top @pageSize*(@pageIndex-1) id from company)

方式二ROW_NUMBER()OVER

--ROW_NUMBER() 就是生成一個(gè)有順序的行號(hào),而他生成順序的標(biāo)準(zhǔn),就是后面緊跟的OVER(ORDER BY ID)
--還必須添加OVER語句以便告訴SQL Server你希望怎樣添加行序號(hào)。
select getdate()
select * from company where id in (
--搜索出settable表中所有的編號(hào)，也就是company表中的id，這里只不過要得到num（即有順序的編號(hào)）
select id from
--搜索出出表中的所有的id，并且新建一列num用來存取排序的編號(hào)，并且把這張表賦值給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中,要實(shí)現(xiàn)顯示某一頁,就返回那一頁數(shù)據(jù)的效果的方法實(shí)在不盡人意.網(wǎng)上很多通用的分頁存儲(chǔ)過程,但看著就頭大.如果使用我前面提到的使用in,not in,top來進(jìn)行返回特定頁,特殊的限制又會(huì)比較多(比如ID要遞增).現(xiàn)在Sql Server 2005中提供了一個(gè)函數(shù)ROW_NUMBER(),可以使自定義分頁變得簡(jiǎn)單許多.
我們先來看看ROW_NUMBER()是干什么的.執(zhí)行下面這段SQL語句:
SELECT [ReportID],[UserName], [ReportID],
[TimeStart], [TimeEnd],ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
FROM [ExecutionLog]
很簡(jiǎn)單,ROW_NUMBER() 就是生成一個(gè)順序的行號(hào),而他生成順序的標(biāo)準(zhǔn),就是后面緊跟的OVER(ORDER BY ReportID).現(xiàn)在,你看到了自定義分頁的影子了嗎?:)下面,我們看看怎么具體應(yīng)用這個(gè)RowNo進(jìn)行分頁.
現(xiàn)在,假設(shè)我每一頁的數(shù)據(jù)是10條,我們就可以使用如下所示的SQL語句返回指定頁的數(shù)據(jù):
@"
SELECT TOP 10 *
FROM
(
SELECT top 10 [InstanceName], [UserName], [ReportID],
[TimeStart], [TimeEnd],ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
FROM [ExecutionLog]
) AS A
WHERE RowNo > " + pageIndex*10
pageIndex就是我們需要數(shù)據(jù)的頁數(shù).很簡(jiǎn)單,不是嗎?并且,這種方式幾乎沒有什么限制,因?yàn)樗喈?dāng)于對(duì)于任何檢索,都生成了一個(gè)新的排序列.我們就可以使用該列進(jìn)行自定義分頁.
================
下面舉個(gè)例子:
ROW_NUMBER函數(shù)
　　SQL Server2005為我們引入了一個(gè)ROW_NUMBER函數(shù)。你是否曾經(jīng)需要為你的查詢結(jié)果集做行序號(hào)?你有時(shí)會(huì)發(fā)現(xiàn)能夠?yàn)樾凶鲂蛱?hào)是一件很有用的事情。從前，你不得不作棘手的事，像創(chuàng)建一個(gè)有序號(hào)列的臨時(shí)表，然后把你的SELECT結(jié)果插入到這個(gè)臨時(shí)表中。現(xiàn)在，用ROW_NUMBER函數(shù)，你就可以獲得添加在你的結(jié)果集的增加列中的行序號(hào)。為了獲得行序號(hào)，你只要簡(jiǎn)單的將ROW_NUMBER函數(shù)作為一列添加進(jìn)你的SELECT語句中。你還必須添加OVER語句以便告訴SQL Server你希望怎樣添加行序號(hào)。
　　SELECT ROW_NUMBER() OVER(ORDER BY employee_id) AS 'Row Number', * from
　　dbo.employee

結(jié)果
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

　　這個(gè)查詢返回所有的雇員和一個(gè)顯示每條記錄在哪一行的一個(gè)序號(hào)。OVER語句使SQL Server基于employee_id列增加行序號(hào)。換句話說，產(chǎn)生了行序號(hào)，就好像數(shù)據(jù)按employee_id做了排序。這是很重要的一點(diǎn)，因?yàn)槟闳匀豢梢愿淖僑ELECT的排序順序。以下面的查詢?yōu)槔?
　　SELECT ROW_NUMBER() OVER(ORDER BY employee_id) AS 'Row Number', * from
　　dbo.employee
　　ORDER BY soc_sec

結(jié)果
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

　　注意第二個(gè)結(jié)果集數(shù)據(jù)是按社會(huì)安全編號(hào)來排序的，但是行號(hào)仍然創(chuàng)建得好像數(shù)據(jù)是按employee_id排序的。
-------------------------------分頁存儲(chǔ)過程
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值返回記錄總數(shù), 非 0 值則返回記錄
)
AS
begin tran
IF @DoCount=0
Goto GetCount
Else
Goto GetSearch
GetCount: --返回記錄總數(shù)
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

最新評(píng)論