mysql - 如何选择一个查询中的列不在另一查询的列之间的记录？

Question

我需要从 QUERY1 中获取 时间 介于 QUERY2 的 timeFrom 和 timeTo 之间的记录

1. 如何合并这两个查询？最有效的方法是什么？

2. 我认为从t1中选择时间在timeFrom和timeTo<之间的记录会更有效 来自 QUERY2，然后才与 t2 连接，对吗？如果是，我该如何通过一个查询来完成它？

注意:表t1有大约200万条记录!

查询1

SELECT ..., time
FROM t1, t2
WHERE t1.time >= t2.starttime

查询2

SELECT timeFrom, timeTto
FROM (...) t3 RIGHT JOIN 
     (...) t4 ON t3.rownum = t4.rownum

Answer 1

进行三向连接:

SELECT ...
FROM t1
JOIN t2 ON t1.time >= t2.starttime
JOIN (SELECT timeFrom, timeTo
      FROM (...) t3
      RIGHT JOIN (...) t4 ON t3.rownum = t4.rownum) t5
ON t1.time BETWEEN t5.timeFrom AND t5.timeTo

让查询调度程序确定执行连接的顺序。但如果这不起作用，您可以尝试通过重新排序到子查询中来帮助它:

SELECT ...
FROM (SELECT t1.*
      FROM t1
      JOIN (SELECT timeFrom, timeTo
            FROM (...) t3
            RIGHT JOIN (...) t4 ON t3.rownum = t4.rownum) t5
      ON t1.time BETWEEN t5.timeFrom AND t5.timeTo) t1
JOIN t2 ON t1.time >= t2.starttime

此外，请确保您在 t1.time 上有索引。

对于 NOT BETWEEN，您需要使用外连接或不存在查询:

SELECT id, time
FROM myTable t
LEFT JOIN filterTable ft ON t.time BETWEEN ft.fromTime AND ft.toTime
WHERE ft.fromTime IS NULL

SELECT id, time
FROM myTable t
WHERE NOT EXISTS (SELECT * FROM filterTable
                  WHERE t.time BETWEEN fromTime AND toTime)

这类似于您必须使用的查询来查找表中在另一个表中没有匹配行的行。唯一的区别是条件是 BETWEEN 而不是 =。

DEMO