面临 SQL 查询中 和/或子句的逻辑处理顺序问题

Question

我正在创建一个#Results表并使用以下脚本插入某些值：

create table #Results
(
ResOID int identity(1,1),
FndAbbr varchar(16),
ResVal varchar(100)
)
insert into #Results
values ('Na', '110'), ('Na', '130'), 
('K', '5.02'), ('K', '5.18'), ('K', '3.60'), ('K', '7.00'); 

现在，如果我执行此查询：

select * 
from #Results
where 
--(FndAbbr = 'NA' and (convert(int, ResVal) < 120 or convert(int, ResVal) > 160))
--or
(FndAbbr = 'K' and (convert(float, ResVal) < 3.0 or convert(float, ResVal) > 6.0))

或查询：

select * 
from #Results
where 
(FndAbbr = 'NA' and (convert(int, ResVal) < 120 or convert(int, ResVal) > 160))
--or
--(FndAbbr = 'K' and (convert(float, ResVal) < 3.0 or convert(float, ResVal) > 6.0))

它工作正常。

但是，当我取消注释这两行并执行查询时：

select * 
from #Results
where 
(FndAbbr = 'NA' and (convert(int, ResVal) < 120 or convert(int, ResVal) > 160))
or
(FndAbbr = 'K' and (convert(float, ResVal) < 3.0 or convert(float, ResVal) > 6.0))

我收到错误

将 varchar 值"5.0"转换为数据类型 int 时转换失败

为什么当"或"变得功能时，它的行为会有所不同？另外，需要进行哪些更改才能使查询正常工作？

Answer 1

此错误是由于您无法在 sql 服务器中将浮点数转换为 Int 的列类型Reseal试试这个：

insert into #Results
values ('Na', '110'), ('Na', '130'), 
('K', '5'), ('K', '5'), ('K', '3'), ('K', '7');

它工作正常。

如果您不想更改 Result 中的数据，这是另一种方法，您可以使用FLOOR或CEILING函数将 ResVal 转换为 int 。

select * from #Results
where 
(FndAbbr = 'NA' and CEILING(CAST(ResVal as float))<120 )
or
(FndAbbr = 'K' and(convert(float,ResVal)<3.0 or convert(float,ResVal)>6.0))

Answer 2

在您的第一个查询中，我怀疑 SQL Server 仅对 FndAbbr = 'NA' 的记录应用convert(int, ResVal)。或者换句话说，我相信在检查第二个条件之前，首先将记录过滤到FndAbbr = 'NA'的记录。

在您的数据集中，ResVal 在FndAbbr = 'NA'时是有效的整数，但在FndAbbr is 'K'时不是('5.02'、'5.18' 等是有效的浮点数，但不是整数(

在您的第三个查询中，我怀疑包含FndAbbr = 'K'记录也会导致根据这些记录评估convert(int, ResVal)，从而导致错误。

Answer 3

您可以使用如下try_convert。如果强制转换成功，它将值强制转换为指定的数据类型;否则为空。

select * 
from #Results
where 
(FndAbbr = 'NA' and 
(try_convert(int, ResVal) < 120 or try_convert(int, ResVal) > 160))
or
(FndAbbr = 'K' and 
(try_convert(float, ResVal) < 3.0 or try_convert(float, ResVal) > 6.0)
)

Answer 4

SQL Server 不保证在SELECT中的计算之前进行WHERE中的筛选。 事实上，SQL是一种描述性语言。 SQL 查询描述结果集。 它不指定操作的顺序。

对于子查询和 CTE 也是如此。 SQL 引擎可以自由地重新排列操作 - 如果它满足查询的需要。

转换可以在查询计划的不同位置进行。 特别是，SQL Server 会将转换推送到读取数据的节点。 这被认为是一种优化 - 这很好。 我确实认为错误处理错误是一个错误，尽管Microsoft显然不同意我的观点。

所有受支持的 SQL Server 版本都支持TRY_CONVERT()和TRY_CAST()来解决此问题：

where (FndAbbr = 'NA' and (try_convert(int, ResVal) < 120 or try_convert(int, ResVal) > 160)) or
(FndAbbr = 'K' and (try_convert(float, ResVal) < 3.0 or try_convert(float, ResVal) > 6.0))

在旧版本中，您可以使用case表达式来执行几乎相同的操作。 SQL确实保证了case表达式中子句的计算顺序。

但更好的解决方案是使用正确的类型存储数据。 在这种情况下，我会建议使用某种数字。