我在这里大大简化了我的示例，所以如果它对于我想要解决的问题来说看起来很简单，请原谅。
是否可以根据行值将 BETWEEN 转换为 NOT BETWEEN ，而不重复 Between 语句？
我理解使用 case 语句和布尔逻辑来帮助使静态 sql 更加“动态”，但我似乎无法找到像 BETWEEN 这样的运算符的方法。
也许使用一些巧妙的按位运算？
我正在开发一些基于集合的 SQL，它们已经变得难以管理，因此我希望通过避免重复来简化它。
我正在尝试以基于 SET 的方式执行此操作，如果可能的话避免使用 UDF。
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”（我试图避免 stringy-dynamic 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'
)
I've simplified my example here massively, so please excuse it if it looks to simple for what I'm trying to solve.
Is it possible to turn BETWEEN, into a NOT BETWEEN depending on a row value (i've used a variable here for simplicity), without duplicating the between statement?
I understand the use of case statements and boolean logic to help make static sql more "dynamic", but I can't seem to figure out a way for operators like BETWEEN.
Perhaps using some clever bitwise operation?
I have some set based SQL in development that is becoming difficult to manage already, so I'm hoping to simplify it by avoiding duplication.
I'm trying to do it in a SET based way, avoiding UDF's if possible.
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')
So in the example above, if @is = 1, then bob is returned.
If @is = 0, then fred is returned.
EDIT:
To clarify, the above statement works fine, but I'm attempting to optimise it to avoid having to write 2 BETWEEN statements. I wish to flip the logic depending on the value of @is.... somewhere using is to put in the NOT.
EDIT 2:
Please see below for some pseudo code for what I'm trying to achieve. It's pseudo, as t-SQL doesn't allow "NOT" to be "injected" in the code in a set based operation like this (I'm trying to avoid stringy-dynamic 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'
)
发布评论
评论（3）
我认为你只需要更多的括号...
编辑
明白了 - 所以然后是这样的：
I think you just need more parens...
EDIT
Got it -- so then something like this:
希望这会有所帮助。
Hope this will be of helpful.
只需使用逻辑将它们组合成一个语句：
这表示双方的“真相”必须相同（要么都是假，要么都是真），这就是您的代码可以简化的结果。
Just use logic to combine them into one statement:
This says that the "truth" of both sides must be the same (either both false or both true), which is what your code can be reduced down to.