mysql - 根据几个 "master date"，找到另一个表中存储的最近的日期，数据共同但没有共同的ID

Question

抱歉，这个标题很难用几句话解释清楚(这表明我没有完全理解这个问题)。

Service Table: (there are other field but not relevant here)
-------------------------------
| PK1    | REF     | Ops_Date |
-------------------------------
| 1      | ABCD    | 20180211 |
| 2      | EFGH    | 20180315 |
| 3      | ABCD    | 20180412 |
-------------------------------

Dim Table: 
-------------------------------
| PK2    | REF     | Arrival  |
-------------------------------
| 57     | ABCD    | 20180108 |
| 58     | ABCD    | 20180201 |
| 59     | EFGH    | 20180309 |
| 60     | EFGH    | 20180311 |
| 61     | ABCD    | 20180409 |
| 62     | ABCD    | 20180411 |
-------------------------------

Result:
--------------------------------------------------------
| PK1    | REF     | Ops_Date | PK2      | Arrival     |       
--------------------------------------------------------
| 1      | ABCD    | 20180211 | 58       | 20180201    |
| 2      | EFGH    | 20180315 | 60       | 20180311    | 
| 3      | ABCD    | 20180412 | 62       | 20180411    |
--------------------------------------------------------

我需要的是服务表与暗淡表的左连接。ON 的标准是两者共有的 REF，然后是距离 ops_date 最近的到达。操作日期将始终在到达之后，这就是我需要始终在操作日期之前最接近的日期。

我尝试的:

   SELECT 
      PK1, 
      REF,
      ops_date, 
      PK2, 
      arrival

   FROM 
     service 

   LEFT JOIN dim
   ON service.REF = dim.REF

我认为应该在此处添加一个条件，以便在左连接期间找到每条记录的最近日期

我不确定这种想法在没有任何 ETL 或程序的情况下是否可能，但如果有人有线索，我们将不胜感激。

预先感谢您的帮助。

Answer 1

您只需在 JOIN 中添加一个条件，表示 Dim 中的 Arrival 时间是最大 Arrival 时间小于该 Service 的关联 Ops_Date 值。您可以使用相关子查询找到该结果:

SELECT s.*, d.PK2, d.Arrival
FROM Service s
LEFT JOIN Dim d ON d.REF = s.REF 
               AND d.Arrival = (SELECT MAX(Arrival)
                                FROM Dim d2
                                WHERE d2.REF = s.REF
                                  AND d2.Arrival < s.Ops_Date)

输出:

PK1 REF     Ops_Date    PK2 Arrival
1   ABCD    20180211    58  20180201
2   EFGH    20180315    60  20180311
3   ABCD    20180412    62  20180411

Demo on dbfiddle