MYSQL - 连接到右表上的最后一条记录 PREVIOUS 从左表记录

Question

我见过很多与此类似的问题，但不太像这样。我需要做的是 LEFT JOIN 记录，到右表上的前一条记录。例如:

LEFT TABLE 'PROD':
idMachine |production | prod.date
1         |10         | 2016-05-02
2         |11         | 2016-05-15
3         |14         | 2016-06-01
3         |13         | 2016-06-15

RIGHT TABLE 'CONFIG':
idMachine | sett   | config.date
1         |  30    | 2016-04-01
2         |  25    | 2016-04-01
3         |  20    | 2016-04-01
1         |  40    | 2016-05-01
3         |  45    | 2016-06-10
1         |  50    | 2016-06-15

and the result should be:
idMachine |production | sett   | prod.date
1         |10         | 40     | 2016-05-02
2         |11         | 25     | 2016-05-15
3         |14         | 20     | 2016-06-01
3         |13         | 45     | 2016-06-15

系统是这样工作的，我记录了机器上的所有生产运行，我记录了每台机器配置上的每一个变化，我需要知道的是它对那个生产运行有什么设置。如果您注意到，在结果中，对于机器 1 和 3，连接不是针对配置的最新记录，而是针对生产发生之前的最后记录，这样我们就可以知道我们使用什么设置生产了什么。

我尝试使用 JOIN CONFIGS ON prod.date > config.date 加入，但它加入了之前的每个配置，而不仅仅是最后一个。如果我使用 LIMIT，它不会给我我需要的所有记录。我已经尝试使用 MAX 对子查询进行一些 JOINS，但是子查询只返回最新的配置，而不是以前的配置，所以“旧”运行没有配置...

非常感谢任何帮助!!!

Answer 1

子查询还需要是两个表的 JOIN，以允许您在 CONFIG 中为每个 PROD 获取之前的日期。然后再次将该子查询包含在与表的连接中以查找 sett值(value)。

像这样:

SELECT p2.idMachine, p2.production, c2.sett, p2.date
FROM PROD AS p2
INNER JOIN
   (SELECT p.idMachine, p.`date` AS prodDate, MAX(c.`date`) AS prev
    FROM PROD AS p 
    INNER JOIN CONFIG AS c 
       ON p.idMachine = c.idMachine AND p.`date` >= c.`date`
    GROUP BY p.idMachine, prodDate
) AS prevs
   ON p2.idMachine = prevs.idMachine AND p2.`date` = prevs.prodDate
INNER JOIN CONFIG AS c2
   ON prevs.idMachine = c2.idMachine AND prevs.prev = c2.`date`

如果你有一个 WHERE子句来过滤您关心的 PROD 记录，您可能希望将它同时放在子查询和主查询中……等一下……

---

根据您的数据“塑造”的方式，这个版本少了一个连接可能会更好。

SELECT p2.idMachine, p2.production, c2.sett, p2.date
FROM (SELECT p.idMachine, p.production, p.`date`, MAX(c.`date`) AS prevConfigDate
    FROM PROD AS p 
    INNER JOIN CONFIG AS c 
       ON p.idMachine = c.idMachine AND p.`date` >= c.`date`
    GROUP BY p.idMachine, p.production, p.`date`
) AS p2
INNER JOIN CONFIG AS c2
   ON p2.idMachine = c2.idMachine AND p2.prevConfigDate = c2.`date`

但是，这里的子查询计算每个 PROD 的 MAX，而不是每个 (idMachine, date)；所以它可能比额外的连接成本更高。但是在这种情况下，多余的 WHERE不需要子句(子查询中只需一个副本)。

---

我继续......还有一个相关的子查询版本，虽然我不喜欢它们......

SELECT p.*
    , (SELECT sett 
       FROM CONFIG AS c 
       WHERE c.idMachine = p.idMachine 
          AND c.date <= p.date 
       ORDER BY c.date DESC LIMIT 1
      ) AS sett
FROM PROD AS p
;

另请注意，在所有这些中，我将日期与 >= 进行了比较或 <= , 不是 <或 > ;您可能需要根据您的实际数据/逻辑进行调整。