last_Value函数的使用（SQL SERVER 2012RC)

225025

　　时常， 我们想取回最值记录， 比如，最后日期什么的，
　　看一个例子，

if OBJECT_ID('test','u') is not null
drop table test
go
create table test(
Id int,
Name nvarchar(50),
Date date,
Amount int)
go
insert into test(Id,Name,Date,Amount)
values
(1,'张三','2011-01-22',15),
(1,'张三','2011-01-28',20),
(2,'李四','2011-01-02',5),
(2,'李四','2011-01-18',8),
(2,'李四','2011-02-14',17)
go
select * from test
　　

　　
　　现在， 我们想要最后日期的记录， 可以使用LAST_VALUE函数，

select Id,Name,Date,Amount,
LAST_VALUE(Id) over(order by Date RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) as LastId,
LAST_VALUE(Name) over(order by Date RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) as LastName,
LAST_VALUE(Amount) over(order by Date RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) as LastAmount
from test
order by Id
　　

　　
　　如果我们想要按照ID分组呢？，非常简单，

select Id,Name,Date,Amount,
LAST_VALUE(Id) over(PARTITION BY ID order by Date RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) as LastId,
LAST_VALUE(Name) over(PARTITION BY ID order by Date RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) as LastName,
LAST_VALUE(Amount) over(PARTITION BY ID order by Date RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING) as LastAmount
from test
order by Id
　　

　　
　　FIRST_VALUE使用和LAST_VALUE一样，