子句之间的多个MySql WHERE [英] Multiple MySql WHERE Between Clauses

问题描述

新手MySql程序员感谢您的耐心等候.

我正在尝试在满足3个不同条件的表中跟踪Id编号，这就是Iv得到的结果，但是查询主体将返回表中明显匹配的任何结果.有想法吗?

SELECT *
FROM `table`
WHERE `x` BETWEEN 80 AND 20
AND `y` BETWEEN 120 AND 20
AND `z` BETWEEN 40 AND 10
LIMIT 0 , 30

我在理论上认为这应该可行吗?

解决方案

关闭，但没有雪茄. :)

SELECT * FROM table 
WHERE (x BETWEEN 20 AND 80) AND 
(y BETWEEN 20 AND 120) AND 
(z BETWEEN 10 AND 40) LIMIT 0 , 30

为说明起见，SQL服务器通常将x BETWEEN val1 AND val2的评估与x >= val1 AND x <= val2相同.原始查询的编写方式，第一个条件是x >= 120 AND x <= 20)，这显然不是您想要的.

不同条件之间的括号可确保在考虑与"之前对每个条件进行完全评估.在大多数情况下，它在SQL中会有所不同，即使不是这样，使用它们也是一个好主意，因此从现在起6个月后，您(或其他人)必须再次查看查询时，您的意图就变得清晰了./p>

Newbie MySql programmer thanks for the patience.

Im trying to track an Id number in a table where 3 different conditions are met this is what Iv got however the query dosent return any results where there are clearly matches in the table. Thoughts?

SELECT *
FROM `table`
WHERE `x` BETWEEN 80 AND 20
AND `y` BETWEEN 120 AND 20
AND `z` BETWEEN 40 AND 10
LIMIT 0 , 30

Am I right in theory to think that this should work?

解决方案

Close, but no cigar. :)

SELECT * FROM table 
WHERE (x BETWEEN 20 AND 80) AND 
(y BETWEEN 20 AND 120) AND 
(z BETWEEN 10 AND 40) LIMIT 0 , 30

To explain, SQL servers generally evaluate x BETWEEN val1 AND val2 the same as x >= val1 AND x <= val2. The way your original query was written, the first condition would be x >= 120 AND x <= 20), which obviously wasn't what you intended.

The parentheses around the different conditions make sure that each is evaluated completely before the AND is considered. It makes a difference most of the time in SQL, and even when it doesn't it's a good idea to use them so your intentions are clear 6 months from now when you (or someone else) has to look at the query again.

这篇关于子句之间的多个MySql WHERE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！