mysql - 如何在 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

还有另一个表 (DailyInterestRates):

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);