你有一个“逻辑优先级”问题。
这些是你的过滤条件:
release_year BETWEEN 1990 AND 2000
AND budget > 1000000000
AND language ='Spanish'
OR language = 'French'
在逻辑运算符中,OR
的优先级低于 AND
,因此这在语法上等价于:
(
release_year BETWEEN 1990 AND 2000
AND budget > 1000000000
AND language ='Spanish'
)
OR language = 'French'
现在您可以看到,这个表达式将允许任何法语电影通过,无论其发行年份和预算如何。
您可能想要:
release_year BETWEEN 1990 AND 2000
AND budget > 1000000000
AND ( language ='Spanish' OR language = 'French')
可以简化为:
release_year BETWEEN 1990 AND 2000
AND budget > 1000000000
AND language IN ('Spanish', 'French')
强制逻辑 and
和 or
的优先级总是一个好主意。
尝试:
SELECT title,release_year
FROM FILMS
WHERE release_year between 1990 AND 2000
AND budget > 1000000000
AND (language ='Spanish' OR language = 'French')
;
或者
SELECT title,release_year
FROM FILMS
WHERE release_year between 1990 AND 2000
AND budget > 1000000000
AND language IN ('Spanish', 'French')
;
最近的MySQL版本对IN
进行了优化,使其比等效的一组OR
条件表现更好。
需要注意的是,我说“总是”是因为即使(a and b) or c
是您想要的逻辑,添加括号也可以让下一个查看查询的人清楚地了解您的意图。
试试这个...
SELECT title,release_year
FROM FILMS WHERE release_year >= 1990
AND release_year <= 2000
AND budget > 1000000000
AND (language ='Spanish' OR language = 'French');
如果您有任何问题,请在下方评论。
IN
的优化评论有参考资料吗?我很想看看他们做了什么(虽然考虑到OR
性能通常很差,他们可能不需要做太多)。 - Nick