mysql - 在查询中,简化

  显示原文与译文双语对照的内容

这是电影表: movies table

这是查询的结果表: results table

查询:


SELECT DISTINCT m.movieID, m.title,s.scheduleDateTime, tf.theaterFeatureID, s.scheduleID, m.releasedDate
FROM theater_feature AS tf 
LEFT JOIN schedule AS s ON s.theaterFeatureID = tf.theaterFeatureID 
INNER JOIN movie AS m ON tf.movieID = m.movieID 
WHERE (s.scheduleDateTime> '2014-10-24' OR s.scheduleDateTime IS NULL)
 AND (tf.theaterFeatureID 
 NOT IN ( SELECT s1.theaterFeatureID FROM schedule AS s1 
 WHERE s1.theaterFeatureID = tf.theaterFeatureID 
 AND s1.scheduleDateTime <= '2014-10-24') )
ORDER BY m.releasedDate, s.scheduleDateTime

由于该查询太慢,我将它的简化为 Having 问题。 它的作用是:

  • 如果电影还没有时间表,则它将显示在特定日期( 但是当前还没有或者还没有通过,在本例中,它是'2014-10-24')的电影,如果电影没有时间表,则应该同时包含该影片。
  • 但是,如果电影通过第一个限定( 带时间表的电影'2014-10-24'。或者,电影没有计划或者是空的),但是有一个已经过去的日期,那电影就不应该被包括在第三个。 简而言之,这个查询是为了即将上映的电影。

在表中,电影"地图到星星"和"迷宫 runner"不包含在结果表中,因为它们已经有一个已经传递的计划。 查询已经很好了,但我希望它可以简化,如果可能的话,替换。

我知道听起来很复杂但是我真的需要找出这个。 如果有人能帮助我的话。

时间: 作者:


SELECT DISTINCT m.movieID, m.title,s.scheduleDateTime, tf.theaterFeatureID, s.scheduleID, m.releasedDate
FROM theater_feature AS tf 
LEFT JOIN schedule AS s ON s.theaterFeatureID = tf.theaterFeatureID 
INNER JOIN movie AS m ON tf.movieID = m.movieID 
WHERE s.scheduleDateTime> '2014-10-24'
ORDER BY m.releasedDate, s.scheduleDateTime

为什么使用 NOT IN 条件,它已经使用这种条件获得了比 scheduleDateTime grater的数据 WHERE s.scheduleDateTime> '2014-10-24'

查看标识是否存在,而不是执行 NOT IN

替代:


 NOT IN ( SELECT s1.theaterFeatureID FROM schedule AS s1 
 WHERE s1.theaterFeatureID = tf.theaterFeatureID 
 AND s1.scheduleDateTime <= '2014-10-24')

执行:


 NOT EXISTS( SELECT s1.theaterFeatureID FROM schedule AS s1 
 WHERE s1.theaterFeatureID = tf.theaterFeatureID 
 AND s1.scheduleDateTime <= '2014-10-24')

编辑完整查询,清晰


SELECT DISTINCT m.movieID, m.title,s.scheduleDateTime, 
 tf.theaterFeatureID, s.scheduleID, m.releasedDate
FROM theater_feature AS tf 
 LEFT JOIN schedule AS s ON s.theaterFeatureID = tf.theaterFeatureID 
 INNER JOIN movie AS m ON tf.movieID = m.movieID 
 WHERE s.scheduleDateTime> '2014-10-24'
 AND NOT EXISTS ( SELECT s1.theaterFeatureID FROM schedule AS s1 
 WHERE s1.theaterFeatureID = tf.theaterFeatureID 
 AND s1.scheduleDateTime <= '2014-10-24') 
 ORDER BY m.releasedDate, s.scheduleDateTime

在所查询的列上添加索引也可以帮助。

作者:
...