将每年可重复的值存储在MySQL数据库中

在过去的几天里,我一直将头撞在桌子上,因此,我转向Stack Overflow.

我正在使用的软件具有时间敏感数据.通常的解决方案是生效和到期日期.

EFF_DT      XPIR_DT     VALUE
2000-05-01  2000-10-31  100
2000-11-01  (null)      90

这很容易.不幸的是,我们要求数据在未来很长时间内任意重复.换句话说,每个5月1日(从2000年开始)我们可能希望有效值为100,而每个11月1日我们可能希望将其更改为90.

可能很长一段时间(大于50年)都是如此,因此我不想只创建一百条记录.即,我不想这样做：

EFF_DT      XPIR_DT     VALUE
2000-05-01  2000-10-31  100
2000-11-01  2001-04-30  90
2001-05-01  2001-10-31  100
2001-11-01  2002-04-30  90
2002-05-01  2002-10-31  100
2002-11-01  2003-04-30  90
...
2049-05-01  2049-10-31  100
2049-11-01  2050-04-30  90
2050-05-01  2050-10-31  100
2050-11-01  2051-04-30  90

这些值也可能随时间变化. 2000年之前的值可能是恒定的(没有触发器),并且未来十年的值可能与上一个十年的值不同：

EFF_DT      XPIR_DT     REPEATABLE  VALUE
1995-01-01  2000-04-30  false       85
2000-05-01  2010-04-30  true        100
2000-11-01  2010-10-31  true        90
2010-05-01  (null)      true        120
2010-11-01  (null)      true        115

我们已经有一个文本文件(来自旧版应用程序),该文件以非常接近此格式的形式存储数据,因此尽可能紧密地遵循这种类型的结构有很多好处.

那么问题就出现在检索上：什么值将适用于今天(2010年3月9日)？

似乎最好的方法是查找(所有活动行中的)每个生效日期的最新实例,然后查看哪个实例最大.

EFF_DT      MOST_RECENT XPIR_DT     VALUE
2000-05-01  2009-05-01  2010-04-30  100
2000-11-01  2009-11-01  2010-10-31  90

由于2009-11-01晚于2009-05-01,因此今天的值是90.

例如,在2007-06-20上：

EFF_DT      MOST_RECENT XPIR_DT     VALUE
2000-05-01  2007-05-01  2010-04-30  100
2000-11-01  2006-11-01  2010-10-31  90

该值将为100,因为2007-05-01晚于2006-11-01.

使用MysqL日期函数,计算MOST_RECENT字段的最有效方法是什么？

或者,有人能想到一种更好的方法吗？

如果重要的话,该语言是Java.谢谢大家！

最佳答案

假设您想要的“日期”为“ 2007-06-20”.

您需要将非重复元素与重复元素组合在一起,因此您可以执行以下操作(未经测试,可能需要一些思考,但应该能为您提供总体思路)：

select * from (
  select * from mytable 
  where 
    repeatable = false
    and 
    EFF_DT <= '2007-06-20' < XPIR_DT
  union all
  select * from mytable
  where
    repeatable = true
    and EFF_DT <= str_to_date(concat("2007","-",month(EFF_DT),day(EFF_DT)),"%Y-%m-%d") < XPIR_DT
)
order by EFF_DT desc limit 1

将每年可重复的值存储在MySQL数据库中

猜你在找的MySQL相关文章