根据多个条件(最接近的值)选择数据

Question

我正在使用MySQL。我正在尝试构建一些东西，但找不到问题的解决方案。

我正在根据lookup表中选择一个值my如下例所示。
选择条件：
my.id<>l.id和my.route1=l.route1，my.route2=l.route2和my.utc=l.utc
哪里my.stime is closest or same as l.stime

例如）my.id=2的col应该得到l.id=1, l.etime=7777，因为my.id<>l.id和其余的是一样的。
例如）自my.id<>l.id, my.route=l.route, my.utc=l.utc年以来，my,id=5 的 col 具有l.id=3, l.etime=9999和l.id=4, l.etime=7979选项。然而，由于my.stime=2220 is closer to l.stime=2222比l.stime=3333，l.id=3, l.etime=9999将被选中。
例如）my,id=6的col示例是在"最接近"相同的情况下选择任一值。
例如）my,id=7的col示例是在不满足条件时返回NULL。

Table: lookup (l.)
id   route1   route2   utc   stime  etime
---|--------|--------|-----|-------|------
1     11        22     111   1111   7777
2     11        22     111   1111   8888
3     22        33     222   2222   9999
4     22        33     222   3333   7979
5     22        33     222   3335   8989
Table: my (my.)                      | result
id   route1   route2   utc   stime   |  l.id   l.etime    
---|--------|--------|-----|-------  |-------|----------|
2     11        22     111   1111    | 1       7777
5     22        33     222   2220    | 3       9999
6     22        33     222   3334    | 4or5    7979or8989
7     22        33     999   9999    | null    null       

应创建一个新表，其中结果将附加到my的最后一个列。任何帮助，不胜感激。提前谢谢。

Answer 1

这个解决方案有点复杂，但它是一个起点。

首先，让我们创建一个辅助表：

CREATE TEMP TABLE temp AS
SELECT m.id mid, l.id lid, ABS(l.stime-m.stime) timediff
FROM my m JOIN lookup l
WHERE m.route1 = l.route1 AND m.route2 = l.route2 AND
      m.utc = l.utc AND m.id <> l.id;

从此表中，我们可以得到每个my.id的最小timediff：

SELECT mid, min(timediff) mtimediff FROM temp GROUP BY mid

结果：

mid         mtimediff
----------  ----------
2           0         
5           2         
6           1         

现在我们可以找到lookup中的哪些行具有此stime差异，并选择最小的id：

SELECT t.mid mid, min(lid) lid
FROM temp t JOIN (
    SELECT mid, min(timediff) mtimediff FROM temp GROUP BY mid
) mt ON t.mid = mt.mid AND t.timediff = mt.mtimediff
GROUP BY t.mid

结果如下：

mid         lid      
----------  ----------
2           1         
5           3         
6           4         

最后，我们使用这些id从表中提取数据：

SELECT m.id, m.route1, m.route2, m.utc, m.stime, l.id, l.etime
FROM my m JOIN lookup l JOIN (
    SELECT t.mid mid, min(lid) lid
    FROM temp t JOIN (
        SELECT mid, min(timediff) mtimediff FROM temp GROUP BY mid
    ) mt ON t.mid = mt.mid AND t.timediff = mt.mtimediff
    GROUP BY t.mid
) ON m.id = mid AND l.id = lid;

给：

id          route1      route2      utc         stime       id          etime     
----------  ----------  ----------  ----------  ----------  ----------  ----------
2           11          22          111         1111        1           7777      
5           22          33          222         2220        3           9999      
6           22          33          222         3334        4           7979