从mysql表中查找给定日期的速率

I have a table:

我有一张桌子:

adate                pdt_id      pdt_rate
2017-10-02           5           20
2017-10-05           5           25
2017-10-07           5           23
2017-10-11           5           20

I have to find the rate of product with pdt_id 5 for between dates 2017-10-01 and 2017-10-10

我必须在2017-10-01和2017-10-10之间找到pdt_id 5的产品率

For 2017-10-01 there is no rate in table so I take the lesser date like 2017-09-30 (no record), 2017-09-29 (no record) and so on (unfortunately no record). So I turn the bigger date than 2017-10-01 is 2017-10-02 (yes, there is a record) so I stop the searching and finalize as

对于2017-10-01,表中没有费率,所以我采取较短的日期,如2017-09-30(无记录),2017-09-29(无记录)等等(不幸的是没有记录)。所以我把2017-10-01的更大日期改为2017-10-02(是的,有记录)所以我停止搜索并最终确定为

2017-10-01  rate is 20

Now find the next date 2017-10-02, there is record and is 20

现在找到2017-10-02的下一个日期,有记录,是20

2017-10-02  rate is 20

Following the similar criteria I got

遵循我得到的类似标准

2017-10-03 rate is  20
2017-10-04 rate is  20
2017-10-05 rate is  25
....
2017-10-10 rate is  23

So my question is How to Find the rate for given date if the date exist other wise take the rate of lesser date and if the lesser date is not exist find the rate of next bigger date?

所以我的问题是如何找到给定日期的费率如果日期存在其他明智的采取较短的日期的比率,如果较小的日期不存在找到下一个更大的日期的比率?

Finally we get rate for all dates(from start date to end date).

最后,我们得到所有日期的费率(从开始日期到结束日期)。

How can I achieve this?

我怎样才能做到这一点?

$begin = new DateTime('2017-10-01');
$end = new DateTime('2017-10-10');

$daterange = new DatePeriod($begin, new DateInterval('P1D'), $end);

foreach($daterange as $date){
   $dateval = $date->format("Y-m-d");
   // here I want find the rate for each date
}

1 个解决方案

#1

Here is one method:

这是一种方法:

select t.*
from t
order by (adate <= '2017-10-01') desc
         abs(datediff(adate, '2017-10-01'))
limit 1;