T-SQL BETWEEN 问题最大值优先

T-SQL BETWEEN problem max value first(T-SQL BETWEEN 问题最大值优先)

问题描述

为什么这两个表达式返回不同的结果?这真的很愚蠢.

Why this two expressions return different results? This is really stupid.

SELECT * FROM Table WHERE ID BETWEEN 3 AND 1

SELECT * FROM Table WHERE ID BETWEEN 1 AND 3

推荐答案

作为 文档说:

如果 test_expression 的值大于，BETWEEN 返回 TRUE或等于 begin_expression 的值且小于或等于end_expression 的值.

BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

没有说明交换 start_expression 和 end_expression 以匹配最小值和最大值.您应该期待记录在案的结果，而不是您认为应该得到的结果.

Doesn't say anything about swapping the start_expression and end_expression to match min and max values. You should expect the result as documented, not as you believe it should.

对于那些好奇的人，ANSI SQL99 标准指定 BETWEEN 谓词应该包括一个用于 SYMMETRIC 或 ASYMMETRIC 比较的子句.只有 SYMMETRIC 允许交换 start_range 和 end_range，ASYMMETRIC 需要严格.ASYMMETRIC 形式是隐式形式.换句话说，将 A BETWEEN X and Y 解释为 (A>=X AND A<=Y) OR (A>=Y AND A<=X)，正如 OP 所暗示的那样，不符合标准.

For the curious out there, the ANSI SQL99 standard specifies that the BETWEEN predicate should include a clause for SYMMETRIC or ASYMMETRIC comparison. Only the SYMMETRIC one is allowed to swap the start_range and end_range, the ASYMMETRIC one is required to be strict. The ASYMMETRIC form is the implicit form. In other words an implementation that interprets A BETWEEN X and Y as (A>=X AND A<=Y) OR (A>=Y AND A<=X), as the OP suggests, is not standard compliant.

这篇关于T-SQL BETWEEN 问题最大值优先的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！