如何在 mysql 中连接两个表，但两者之间存在日期差异?

Question

我有一个表(GaugeCompare(：

Form15 | Form30  | Form60  | GaugeDate
---------------------------------------
float  | caution | caution | 2018-10-08
float  | float   | caution | 2018-10-04
float  | caution | caution | 2018-10-02

和另一个表格(每日利率(：

Rate | RateDate
-----------------
...more data above...
4.875| 2018-09-23
4.875| 2018-09-22
4.75 | 2018-09-21
5    | 2018-09-20
5    | 2018-09-19
5    | 2018-09-18
5    | 2018-09-17
4.875| 2018-09-16
4.75 | 2018-09-15
4.875| 2018-09-14
...more data below...

我想找到一种方法来连接这两个表，基于日期相隔这么多天的差异分别是 15、30 和 60。例如，我希望看到我的表格看起来像这样：

Form15 | Form30  | Form60  | GaugeDate  | RateDate_15 | Rate_15 | RateDate_30 |...
-----------------------------------------------------------------------------------
float  | caution | caution | 2018-10-08 | 2018-09-23  | 4.875   | 2018-09-08  |...
float  | float   | caution | 2018-10-04 | 2018-09-19  | 5       | 2018-09-04  |...
float  | caution | caution | 2018-10-02 | 2018-09-17  | 5       | 2018-09-02  |...

我早上大部分时间都在玩弄它并对此进行搜索，但没有遇到任何重要的东西可以让它为我工作。有人有什么独特的方法来解决这个问题吗？

Answer 1

select GaugeCompare.form15, GaugeCompare.form30, GaugeCompare.form60, GaugeCompare.gaugeDate,
d15.ratedate ratedate_15, d15.rate rate_15,
d30.ratedate ratedate_30, d30.rate rate_30,
d60.ratedate ratedate_60, d60.rate rate_60
from GaugeCompare
left join DailyInterestRates d15 on GaugeCompare.gaugeDate = date_add(d15.rateDate, interval 15 day)
left join DailyInterestRates d30 on GaugeCompare.gaugeDate = date_add(d30.rateDate, interval 30 day)
left join DailyInterestRates d60 on GaugeCompare.gaugeDate = date_add(d60.rateDate, interval 60 day);

Answer 2

这是你想要的吗？

select gc.*, dir_15.ratedate, dir_15.rate,
dir_30.ratedate, dir_30.rate, dir_60.ratedate, dir_60.rate
from GaugeCompare gc left join
DailyInterestRates dir_15
on gc.gaugedate = dir_15.ratedate + interval 15 day left join
DailyInterestRates dir_30
on gc.gaugedate = dir_30.ratedate + interval 30 day left join
DailyInterestRates dir_60
on gc.gaugedate = dir_60.ratedate + interval 60 day;

Answer 3

您可以执行三个外部连接，如下所示：

select
g.Form15, g.Form30, g.Form60, g.GaugeDate,
r15.RateDate as RateDate_15, r15.Rate as Rate_15,
r30.RateDate as RateDate_30, r30.Rate as Rate_30,
r60.RateDate as RateDate_60, r60.Rate as Rate_60
from GaugeCompare g
left join DailyInterestRates r15 on r15.RateDate = g.GaugeDate - interval 15 day
left join DailyInterestRates r30 on r30.RateDate = g.GaugeDate - interval 30 day
left join DailyInterestRates r60 on r60.RateDate = g.GaugeDate - interval 60 day