SQL返回一个特定日期的值 [英] SQL return a value at a specific date in time
本文介绍了SQL返回一个特定日期的值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试在某个日期找到一个值。
I'm trying the find a value at a certain date.
我的数据看起来像
Date Value
2013-11-02 5
2013-10-10 8
2013-09-14 6
2013-08-15 4
如何确定2013-09-30的价值?
How can I determine what the value was on 2013-09-30?
显然答案是6,但我无法弄清楚SQL代码。
Obviously the answer is 6 but I can't figure out the SQL code.
谢谢
推荐答案
您可以通过按顺序执行,并限制行数。在SQL Server语法(和Sybase和Access)中:
You can do it with order by
and limiting the number of rows. In SQL Server syntax (and Sybase and Access):
select top 1 t.*
from table t
where date <= '2013-09-30'
order by date desc;
在MySQL(和Postgres)中:
In MySQL (and Postgres):
select t.*
from table t
where date <= '2013-09-30'
order by date desc
limit 1;
在Oracle中:
select t.*
from (select t.*
from table t
where date <= '2013-09-30'
order by date desc
) t
where rownum = 1
编辑:
而且,一个SQL标准的方法(应该在任何数据库中工作):
And, a SQL standard way to it (should work in any database):
select t.*
from table t
where date = (select max(date)
from table t2
where date <= '2013-09-30'
);
这篇关于SQL返回一个特定日期的值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文