在另一个之前执行 WHERE 子句 [英] Execute a WHERE clause before another one

问题描述

我有以下声明

SELECT * FROM foo
WHERE LEN(bar) = 4 AND CONVERT(Int,bar) >= 5000

bar 中长度正好为 4 个字符的值为整数.其他值不是整数，因此在尝试将其中一个值转换为整数时会引发转换异常.

The values in bar with a length of exactly 4 characters are integers. The other values are not integers and therefore it throws an conversion exception, when trying to convert one of them to an integer.

我认为将 LEN(bar) 放在 CONVERT(Int,bar) >= 5000 之前就足够了.但事实并非如此.

I thought it's enough to put the LEN(bar) before the CONVERT(Int,bar) >= 5000. But it's not.

我如何确定特定 where 子句的优先级?在我的示例中，我显然想在转换和比较它们之前选择长度为 4 的所有值.

How can I kind of prioritize a specific where clause? In my example I obviously want to select all values with a length of 4, before converting and comparing them.

推荐答案

6 个答案，其中 5 个不起作用(对于 SQL Server)...

6 answers and 5 of them don't work (for SQL Server)...

SELECT *
  FROM foo
 WHERE CASE WHEN LEN(bar) = 4 THEN
       CASE WHEN CONVERT(Int,bar) >= 5000 THEN 1 ELSE 0 END
       END = 1;

WHERE/INNER JOIN 条件可以按查询优化器确定的最佳顺序执行.没有短路布尔评估.

The WHERE/INNER JOIN conditions can be executed in any order that the query optimizer determines is best. There is no short-circuit boolean evaluation.

特别是对于您的问题，既然您知道 4 个字符的数据是一个数字，那么您可以进行直接的词典(文本)比较(是的，它有效):

Specifically for your question, since you KNOW that the data with 4-characters is a number, then you can do a direct lexicographical (text) comparison (yes it works):

SELECT *
  FROM foo
 WHERE LEN(bar) = 4 AND bar > '5000';

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