mysql - 优先考虑 MySQL 中的左连接行

Question

我正在处理 MySQL (5.5) 中的一些稍微复杂的数据。我有一个用于跟踪状态(新、事件、非事件等)和“位置”更改的表，其中位置的格式为 x.0.y，x 和 y 为正整数。

我正在尝试将位置更改移动到一个单独的表中，但为了这样做，我需要从表中的其他记录中恢复完整的旧位置。完整的旧位置未存储在位置更改记录中，如下所示:

id      status      new_loc     timestamp
--      ------      -------     ---------
5       1 -> 1      1.0.2       2012-05-21 00:00:00
5       new         1.0.1       2012-05-21 00:00:03
5       1 -> 2      2.0.1       2012-05-22 00:00:00
5       2 -> 3      3.0.1       2012-05-23 00:00:00

因此，当发生位置更改时，记录的只是完整的新位置和旧位置的第一部分(状态消息的左侧)。但是，我可以查看以前的记录以找到完整的旧位置。

我的主要问题是那里的第二行。 "new"状态意味着 ID 为 5 的对象刚刚添加到数据库中。它应该始终是表中任何 id 的第一个状态。但是，在许多情况下，"new"记录会在初始更改记录后大约三秒插入。在本例中，添加位置为 1.0.1，然后更改为 1.0.2，然后更改为 2.0.1，然后更改为 3.0.1。 (编辑:真实示例并非如此简单或直截了当，但总有一条人类可以相当容易辨别的位置“路径”。)

我在尝试编写一个同时考虑第 1 行和第 3 行的查询时遇到了问题。我不能只找到最近的记录，因为它们可能是乱序的。我不能只用与我的状态匹配的 new_loc 获取记录，因为可能有多个匹配项。

这是我认为可行的方法:

1. 查找最近的先前位置更改(如果存在)，并使用其 new_loc。
2. 如果之前不存在更改，则找到该 ID 状态为"new"的记录并使用其 new_loc。

我目前有一个匹配这两个条件的 LEFT JOIN，但我不知道如何说“如果没有匹配条件 1，则仅在条件 2 上加入”或“更喜欢在条件 1 上加入行。”

我只是不知道该怎么做。感谢您的帮助。

编辑:我应该明确 id 是一个对象 id，显然不仅仅是这个表的索引。主键是 (id, status)。

Answer 1

让我确认一下，我了解您的意图。在伪代码中，这会做你正在寻找的吗？

given
   table A (as in your answer)
   table B (objectid, old, new)
for any `id`
   get all rows
   order rows by `status` column such that 'new' is the first value, and then numerically
   take the location in the first `status` column c and insert it into table B with values id, 'new', (c).location
   for all remaining `status` fields selected: 
       for any field n, insert into table B with values id, (n-1).location, (n).location

这应该可以通过存储过程实现……但是编写代码可能会很痛苦。