mysql - 如何动态地从另一个表中检索最近的日期 MySQL

Question

我有两个表:

INFO 表

ID        LockDate            Investor 
157       10/15/2018          TEST1
VF1       09/02/2018          TEST2
LO2       05/01/2018          TEST3
09K       03/03/2012          TEST4
098       05/01/2012          TEST5
099       09/09/2012          TEST6
2YT       08/25/2012          TEST7

NUMBERS 表

Dates              Amount
10/10/2018         25.10
08/31/2018         200.15
05/10/2018         15.251
03/03/2012         10.10
05/10/2012         15.251
08/31/2012         548.0          

我希望查询选择 INFO 表中的所有值，并在 Numbers 表中找到最接近或相等的日期并提供金额。所以我的结果是:

ID        LockDate            Investor         Amount
157       10/15/2018          TEST1            25.10
VF1       09/02/2018          TEST2            200.15
LO2       05/01/2018          TEST3            15.251
09K       03/03/2012          TEST4            10.10
098       05/01/2012          TEST5            15.251
099       09/09/2012          TEST6            548.0
2YT       08/25/2012          TEST7            548.0

最接近的意思是等于或最接近锁定日期小于或大于。

这是我正在使用的查询，但它只是检索大于或等于的查询，这对我来说根本不起作用，因为我必须动态执行...

SELECT I.* ,
       N.Amount  FROM 
(
SELECT  A.*, MIN(NUM.Dates) AS XDATE
FROM INFO A
INNER JOIN NUMBERS AS NUM
ON NUM.Dates >= A.LockDate
GROUP BY A.ID
)AS RES
INNER JOIN NUMBERS AS N
ON N.Dates = I.XDATE  

我将不胜感激。

Answer 1

您只需要找到 LockDate 减去 Dates 的绝对最小值。这将为您提供最接近的日期；更小或更大。休息很容易。

SELECT info.*, numbers.*
FROM info
INNER JOIN (
    SELECT ID, MIN(DATEDIFF(GREATEST(LockDate, Dates), LEAST(LockDate, Dates))) Delta
    FROM info
    CROSS JOIN numbers
    GROUP BY ID
) g ON info.ID = g.ID
INNER JOIN numbers ON DATEDIFF(GREATEST(LockDate, Dates), LEAST(LockDate, Dates)) = g.Delta

SQL Fiddle