T-SQL：根据行值翻转BETWEEN运算符

Question

这里我的示例进行了大量简化，如果它看起来过于简单，请多多包涵，因为我想要解决的问题并不简单。

是否有可能根据行值（我在这里使用一个变量来简化）将BETWEEN转换为NOT BETWEEN，而无需复制between语句?

我知道可以使用case语句和布尔逻辑来帮助使静态SQL更加“动态”，但似乎对于像BETWEEN这样的运算符，我无法找到一种方法。

也许可以使用一些巧妙的位操作?

我正在开发一些基于集合的SQL，已经变得难以管理，所以我希望通过避免重复来简化它。

我尝试以基于集合的方式进行操作，如果可能的话，避免使用UDFs。

DECLARE @is int
set @is = 1

DECLARE @TestTable TABLE
(
    Name varchar(100),
    DOB datetime
)

INSERT INTO @TestTable(Name, DOB)
SELECT 'bob', '2011-08-18 10:10:10.100' UNION ALL
SELECT 'fred', '2014-08-18 10:10:10.100'


SELECT Name, DOB
FROM @TestTable
WHERE
    (@is = 1) AND (DOB BETWEEN '2011-08-18' AND '2012-08-18') 
    OR
    (@is = 0) AND (DOB NOT BETWEEN '2011-08-18' AND '2012-08-18')

在上面的示例中，如果@is = 1，则返回bob。如果@is = 0，则返回fred。

编辑： 为了澄清，上述语句可以正常工作，但我试图优化它以避免编写2个BETWEEN语句。我希望根据@is的值翻转逻辑....在某个地方使用is来放入NOT。

编辑2： 请参见下面的伪代码，了解我想要实现的内容。它是伪代码，因为t-SQL不允许在类似于这样的基于集合的操作中“注入”代码中的“NOT”（我试图避免字符串动态sql）。

SELECT Name, DOB
FROM @TestTable
WHERE
(
    DOB
        CASE @is
            WHEN 0
            THEN NOT ---pseudo code, this won't actually work, but gives an idea of what I'm trying to achieve.
        END
    BETWEEN '2011-08-18' AND '2012-08-18'
) 

- Alex KeySmith

Answer 1

我认为你只需要更多的括号...

WHERE
    (   (@is = 1) AND (DOB BETWEEN '2011-08-18' AND '2012-08-18')   )
    OR
    (   (@is = 0) AND (DOB NOT BETWEEN '2011-08-18' AND '2012-08-18')   )

编辑

明白了——那么像这样的内容：

WHERE @is = CASE WHEN DOB BETWEEN '2011-08-18' AND '2012-08-18' THEN 1 ELSE 0 END

- Chains

---

抱歉kuru，我不认为我在问题上表达得非常清楚。我提出的SQL本身没有问题。但我正在尝试优化它，以避免需要编写2个BETWEEN语句，而是希望BETWEEN语句根据@is变量的值自动翻转为NOT BETWEEN。 - Alex KeySmith

如果有帮助到您那就太好了。 :-) 至于位转换，它只取决于您的变量。由于@is是一个整数，因此它将与1和0一起使用，就像它是一个位一样。但是，如果您对@is的值不是1和0（比如“是”或“否”），那么您只需要将这些值替换为CASE语句中的1和0即可。 - Chains

谢谢提供的信息，事实上是我的笔误，应该是“@bit”而不是“@is”。但听起来好像我不需要类型转换，非常好的答案，谢谢。 - Alex KeySmith

@Martin - 很好的观察。（我认为）你可以在表变量上创建一个聚集索引，但不能创建非聚集索引。而且表变量上也没有统计信息，所以优化（至少对于查询优化器来说）可能不是最大的问题。你让我查了一下“sargable”这个词-谢谢！:-) - Chains

@kuru - 对于问题中的设置，使用堆表变量是可以接受的，但问题提问者确实说这只是一个玩具示例，他们真正的查询不同。 - Martin Smith

Answer 2

希望这对你有所帮助。

    SELECT Name, DOB FROM @TestTable WHERE 
CASE WHEN @is = 1 THEN DOB ELSE '1900' END BETWEEN '2011-08-18' AND '2012-08-18'

- teenboy

---

有趣，谢谢teenboy。kuru提供了一个好的解决方案。但是感谢你的努力，你的看起来也不错，我无法使其正常工作，但我喜欢语法的想法。+1。 - Alex KeySmith

Answer 3

只需使用逻辑将它们组合成一个语句：

(@is = 1) = (DOB BETWEEN '2011-08-18' AND '2012-08-18') 

这意味着双方的“真相”必须相同（两者都为假或两者都为真），这就是您的代码可以简化到的内容。

- Bohemian

---

TSQL不允许您比较布尔语句的结果。你可能会认为它会起作用，但令人惊讶的是这是无效的... - Michael Fredrickson

有趣的建议Bohemian，谢谢。但是我也想返回相反的结果。所以如果@is = 0，仍然返回一个结果。我甚至不确定我正在尝试做的事情是否可以在SQL中表示 :-) - Alex KeySmith