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

最新推荐文章于 2024-12-27 16:41:59 发布

原创最新推荐文章于 2024-12-27 16:41:59 发布 · 3.5k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #date #table #object

SQL 专栏收录该内容

79 篇文章

订阅专栏

本文详细介绍了在SQL中如何使用LAST_VALUE和FIRST_VALUE函数获取特定日期范围内的最大和最小值，并展示了如何按ID分组进行操作。通过实际例子，读者可以学会灵活运用这些函数解决实际问题。

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

看一个例子，

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一样，