我需要从QUERY2的timetimeFrom之间的QUERY1中获取记录


如何合并这两个查询?最有效的方法是什么?
我认为从QUERY2中从timeTot1time之间的timeFrom中选择记录将更加有效,只有在将其与timeTo一起加入之后,对吗?
如果是,我该如何处理一个查询?


注意
:表t2大约有2百万条记录!

QUERY1

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


QUERY2

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

最佳答案

进行三向联接:

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

关于mysql - 如何选择一个查询的列不在另一查询的列之间的记录?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/23825470/

10-09 00:42
查看更多