MSSQL2005新增函数ROW_NUMBER()

 - by Hector

简介

ROW_NUMBER()是MS SQL Server 2005中新增窗口函数,可用来直接生成行号(但是SQL Server 2000中是没有这个函数的)。如:

select ROW_NUMBER() over (order by ENo), ENo,EName  
from Employee 

主要作用

分页

select * from (select orderid,freight,row_number() over(order by freight) as row from Orders) a
where row between 20 and 30

结合通用表达式进行分页

WITH OrderFreight AS( 
    select OrderId, Freight, ROW_NUMBER() OVER(order by Freight) as row from Orders 
) 
select OrderId, Freight from OrderFreight where row between 10 and 20

mssql2000模拟方法

1、使用临时表。

如果是在存储过程中,这是一个不错的选择。
创建一个临时表,其中除了需要的查询结果外,还一个记数列。查询结果放入临时表后(一般情况下可直接使用Insert into语句),用代码进行记数,更新记数列的值。
在记数列数值的生成方法上,还有一个改进的办法是直接将该列定义为自增长字段。这样“记数”的代码也省掉了。

2、采用自连接。

如果是在代码中,不便于使用临时表,可以采用此方法。比如执行一个查询统计,需要一个替代的SQL语句。
这个方法实际上是通过表的自连接给结果行“分等级”的思路来实现的。
语句如下:

select count(*),e1.ENo, e1.EName  
from Employee e1  
inner join Employee e2 on e1.ENo >= e2.ENo  
group by e1.ENo, e1.EName  
order by 1  

或者把join条件放到where子句中:

select count(*),e1.ENo, e1.EName  
from Employee e1, Employee e2,   
where e1.ENo >= e2.ENo  
group by e1.ENo, e1.EName  
order by 1  

如果ENo字段值不重复,还可以这样写:

select (select count(*) from Employee e2 where e1.ENo >= e2.ENo), e1.ENo, e1.EName  
from Employee e1  
order by 1  

如果ENo字段值有重复的情况,要使用最后一种写法可以将where条件变通一下,如:

where e1.ENo+e1.EName >= e2.ENo+e2.EName

总能找到不存在重复值的表达式的。

Leave a comment