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

最新推荐文章于 2025-05-30 09:09:57 发布

最新推荐文章于 2025-05-30 09:09:57 发布 · 616 阅读

本文通过具体实例展示了如何在SQL中使用LAST_VALUE函数来获取指定字段的最后一个值，包括不分组和按ID分组的情况，并简要提及了FIRST_VALUE函数的用法。

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

看一个例子，

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