[sql server] 問題總結6- 提取行號 ROW_NUMBER 分頁

 

在SQL 2000 和 2005下 的不同 方法

 

 

先說 2000下的

 

create table #temp1
(
ID int not null ,
code varchar(10) not null
primary key(ID,Code) 
)

insert into #temp1(ID,code) values(1,'01')
insert into #temp1(ID,code) values(3,'01')
insert into #temp1(ID,code) values(100,'01')
insert into #temp1(ID,code) values(5,'02')

--想得到的結果是裡面的ID更新為按循序，1，2，3，4的 (ID裡面具體那個是1，那個是2，無所謂的)
--要求：SQL2000下面，不能使用暫存資料表或其它表哦
/*
1，01
2，01
3，02
4，01
*/

select * from #temp1

drop table #temp1

方法1：declare @num int
set @num=0
update #temp1 set ID=@num,@num=@num+1方法2：select no=(select count(1) from #temp1 where ID<=A.ID),*
from #temp1 a

--------------------------------------------------------------

 

2005下的方法

 

表
col1 col2 col3
A B 類型A
A C 類型A
A D 類型A
K F 類型A
K C 類型A
K G 類型A
如何?下面功能：
col1 col2 col3
A B 類型A1
A C 類型A2
A D 類型A3

K F 類型A1
K C 類型A2
K G 類型A3

-------------------------

 

 

if not object_id(N'Tempdb..#1') is null
    drop table #1
Go
Create table #1([col1] nvarchar(1),[col2] nvarchar(1),[col3] nvarchar(50))
Insert #1
select N'A',N'B',N'類型A' union all
select N'A',N'C',N'類型A' union all
select N'A',N'D',N'類型A' union all
select N'K',N'F',N'類型A' union all
select N'K',N'C',N'類型A' union all
select N'K',N'G',N'類型A'
Go

 

Select *,
row=[col3]+RTRIM(ROW_NUMBER()OVER(PARTITION BY [col1] ORDER BY [col3]))

from #1

 

/*
col1    col2    col3
A    B    類型A1
A    C    類型A2
A    D    類型A3
K    F    類型A1
K    C    類型A2
K    G    類型A3

*/

 

------------------------------------

文法：
ROW_NUMBER ( )     OVER ( [ <partition_by_clause> ] <order_by_clause> )
備忘：
ORDER BY 子句可確定在特定分區中為行分配唯一 ROW_NUMBER 的順序。
參數
<partition_by_clause>
將 FROM 子句產生的結果集劃入應用了 ROW_NUMBER 函數的分區。
<order_by_clause>
確定將 ROW_NUMBER 值分配給分區中的行的順序。有關詳細資料，請參閱 ORDER BY 子句 (Transact-SQL)。
有關詳細資料，請參閱 OVER 子句 (Transact-SQL)。
例子
一、以下樣本將根據年初至今的銷售額，返回 AdventureWorks 中銷售人員的 ROW_NUMBER。
USE AdventureWorks
GO
SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0
二、以下樣本將返回行號為 50 到 60（含）的行，並以 OrderDate 排序。
USE AdventureWorks;
GO
WITH OrderedOrders AS
(SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (order by OrderDate)as RowNumber
FROM Sales.SalesOrderHeader )
SELECT *
FROM OrderedOrders
WHERE RowNumber between 50 and 60;
三、實現分頁預存程序(固定到某個表的資料)
 
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      cxw
-- Create date: 2006/11/14
-- Description: 利用Row_Number函數實現分頁預存程序
 
-- exec DividePageForROW_NUMBER 2,1,'',''
 
-- =============================================
Create PROCEDURE DividePageForROW_NUMBER
 
@iPageSize      int         --每頁數
,@iIndexPage    int         --第幾頁
,@vOrderBy      varchar(50) --排序欄位
,@iRowCount     int         --總記錄數
 
AS
BEGIN
SET NOCOUNT ON;
with PageTable as
(
select row_number() over(order by ReasonID) TID,* from A
)
select *
from PageTable
where TID between ((@iIndexPage-1)*@iPageSize+1) and @iPageSize*@iIndexPage
 
SET NOCOUNT OFF;
 
END
GO