sqlserver2005使用row_number() over分頁的實現方法

2024-08-31 01:00:55

字體：大中小

來源：轉載

供稿：網友

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

復制代碼代碼如下:

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
就可以實現分頁了呵呵(從此分頁就是這么簡單)

例子:

復制代碼代碼如下:

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], [UserName], [ReportID],
[TimeStart], [TimeEnd],ROW_NUMBER() OVER (ORDER BY ReportID) AS RowNo
FROM [ExecutionLog]
) AS A
WHERE RowNo > " + pageIndex*10
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)
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

上一篇：SQL server 2008 數據安全(備份和恢復數據庫)

下一篇：Java基本類型與包裝類詳細解析