我需要从QUERY2的time
和timeFrom
之间的QUERY1中获取记录
如何合并这两个查询?最有效的方法是什么?
我认为从QUERY2中从timeTo
在t1
和time
之间的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/