SQL选择2个表中另一个日期的最接近日期

Question

我有两个表

Table A
pk name   a_date
1  Peter  01-01-1991
2  Paul   02-01-1991
3  John   03-01-1991
Table B
pk  b_date
A   01-01-1991
B   02-01-1991

我想要的结果是：

name   a_date        b_date
Paul   02-01-1991    01-01-1991
John   03-01-1991    02-01-1991

选择表A

的表B的最接近日期

我尝试在选择

之前绘制结果

1  Peter  01-01-1991  A  01-01-1991
1  Peter  01-01-1991  B  02-01-1991
2  Paul   02-01-1991  A  01-01-1991
2  Paul   02-01-1991  B  02-01-1991
3  John   03-01-1991  A  01-01-1991
3  John   03-01-1991  B  02-01-1991

然后我尝试从a_date> b_date

中选择**

，但会这样返回：

2  Paul   02-01-1991  A  01-01-1991
3  John   03-01-1991  A  01-01-1991
3  John   03-01-1991  B  02-01-1991

我还尝试了一些诸如Sub-Query之类的事情：其中a_date =（select ...）;但是我认为这是一个错误的方法。那么我认为这可能是不同的，可以解决这个问题，但是我发现在这种情况下我无法使用不同的方式。

Answer 1

这应该这样做（假设日期存储为 date而不是字符串）：

SELECT a.name, a.date, MAX(b.date)
FROM a
JOIN b ON b.date < a.date
GROUP BY a.name, a.date

Answer 2

在mysql 7.x中，您可以加入到计算最近的子问题。

SELECT
a.*, 
b.pk as b_pk, 
b.b_date
FROM TableA a
INNER JOIN
(
    SELECT 
     a2.pk as a_pk, 
     MAX(b2.pk) AS b_pk
    FROM TableA a2
    JOIN TableB b2 ON b2.b_date < a2.a_date
    GROUP BY a2.pk
) q ON q.a_pk = a.pk
LEFT JOIN TableB b ON b.pk = q.b_pk

rextester的测试

在MySQL 8中，您可以为此使用窗口函数。

SELECT pk, name, a_date, b_pk, b_date
FROM 
(
    SELECT a.*, 
     b.pk AS b_pk,
     b.b_date,
     ROW_NUMBER() OVER (PARTITION BY a.pk ORDER BY b.b_date DESC) AS rn
    FROM TableA a
    JOIN TableB b ON b.b_date < a.a_date
) q
WHERE rn = 1

db-fiddle 的测试