mysql - 在两个条目之间的日期加入

Question

我有以下两个表:

交付为 t1

date,               driver_id

2016-01-01 13:44:22, 32
2016-01-02 13:14:58, 32
2016-03-05 13:14:55, 32

DELIVERY 表有收货时间的记录。

和

DRIVER_DISPATCH_HISTORY 作为 t2

date,               driver_id, dispatch_id

2016-01-01 19:12:23, 32,        4
2016-01-07 11:16:58, 32,        3
2016-03-06 13:14:55, 32,        7
2016-03-06 16:54:12, 33,        8

DRIVER_DISPATCH_HISTORY 表跟踪驱动程序何时被分配 dispatch

我的主要目标是JOIN t1.date=t2.date AND t1.driver_id=t2.driver_id得到driver_id

问题历史表不是每天跟踪分配，而是使用间隔。如果我在一个月前分配司机 dispatch ，它将只有一个条目。如果我今天更改该司机的调度，它会在今天添加另一个条目，而且，如果我多次更改调度，它会在同一天添加多个具有不同时间戳的条目，我需要在分配之间匹配交货时间.

问题:虽然内部联接不是问题，但我无法找到实现上述逻辑的解决方案。

SELECT 
 t1.date, t1.driver_id, t2.dispatch_id
FROM 
 DELIVERY as t1
INNER JOIN 
 DRIVER_DISPATCH_HISTORY as t2
  ON t1.date=t2.date 
   AND 
     t1.driver_id=t2.driver_id

假设 driver_id 和 dispatch_id 是匹配的，并且可能有很多交付

Answer 1

您想要的是低于交货日期的最高发货日期。编写一个查找此日期的子查询，然后将其与调度历史表连接起来以获取调度 ID。

SELECT x.driver_id, x.date, y.dispatch_id
FROM (
    SELECT t1.driver_id, MAX(t2.date) AS dispatch_date, t1.date
    FROM delivery AS t1
    JOIN driver_dispatch_history AS t2 
    ON t1.driver_id = t2.driver_id AND t1.date > t2.date
    GROUP BY t1.driver_id, t1.date) AS x
JOIN driver_dispatch_history AS y
ON x.driver_id = y.driver_id AND x.dispatch_date = y.date