[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