T-SQL中的布尔值'NOT'不工作于'bit'数据类型?

Trying to perform a single boolean NOT operation, it appears that under MS SQL Server 2005, the following block does not work

尝试执行一个布尔不操作，在SQL Server 2005中，下面的块不工作。

DECLARE @MyBoolean bit;
SET @MyBoolean = 0;
SET @MyBoolean = NOT @MyBoolean;
SELECT @MyBoolean;

Instead, I am getting more successful with

相反，我越来越成功了

DECLARE @MyBoolean bit;
SET @MyBoolean = 0;
SET @MyBoolean = 1 - @MyBoolean;
SELECT @MyBoolean;

Yet, this looks a bit a twisted way to express something as simple as a negation.

然而，用这种方式来表达像否定一样简单的东西，看起来有点扭曲。

Am I missing something?

我遗漏了什么东西?

7 个解决方案

#1

134

Use the ~ operator:

使用~运算符:

DECLARE @MyBoolean bit
SET @MyBoolean = 0
SET @MyBoolean = ~@MyBoolean
SELECT @MyBoolean

#2

Your solution is a good one... you can also use this syntax to toggle a bit in SQL...

你的解决办法很好……您还可以使用此语法在SQL中切换一些……

DECLARE @MyBoolean bit;
SET @MyBoolean = 0;
SET @MyBoolean = @MyBoolean ^ 1; 
SELECT @MyBoolean;

#3

Subtracting the value from 1 looks like it'll do the trick, but in terms of expressing intent I think I'd prefer to go with:

从1中减去这个值看起来很有用，但是在表达意图方面，我想我更喜欢:

SET @MyBoolean = CASE @MyBoolean WHEN 0 THEN 1 ELSE 0 END

It's more verbose but I think it's a little easier to understand.

它更冗长，但我认为它更容易理解。

#4

In SQL 2005 there isn't a real boolean value, the bit value is something else really.

在SQL 2005中没有真正的布尔值，位值实际上是其他东西。

A bit can have three states, 1, 0 and null (because it's data). SQL doesn't automatically convert these to true or false (although, confusingly SQL enterprise manager will)

位可以有三种状态，1、0和null(因为它是数据)。SQL不会自动将这些转换为真或假(尽管，令人困惑的是，SQL enterprise manager会)

The best way to think of bit fields in logic is as an integer that's 1 or 0.

在逻辑中，最好的理解位字段的方法是把它看成是1或0的整数。

If you use logic directly on a bit field it will behave like any other value variable - i.e. the logic will be true if it has a value (any value) and false otherwise.

如果您直接在位字段上使用逻辑，它的行为将与任何其他值变量一样——例如，如果逻辑具有值(任何值)，逻辑将为真，否则为假。

#5

To assign an inverted bit, you'll need to use the bitwise NOT operator. When using the bitwise NOT operator, '~', you have to make sure your column or variable is declared as a bit.

要分配一个倒位，你需要使用位而不是运算符。当使用bitwise NOT运算符“~”时，您必须确保您的列或变量被声明为比特。

This won't give you zero:

这不会给你零:

Select ~1

This will:

这将:

select ~convert(bit, 1)

So will this:

所以这将:

declare @t bit
set @t=1
select ~@t

#6

BIT is a numeric data type, not boolean. That's why you can't apply boolean operators to it.
SQL Server doesn't have BOOLEAN data type (not sure about SQL SERVER 2008) so you have to stick with something like @Matt Hamilton's solution.

位是数字数据类型，而不是布尔数据类型。这就是为什么不能对它应用布尔运算符的原因。SQL Server没有布尔数据类型(不确定SQL Server 2008)，所以您必须坚持使用类似于@Matt Hamilton的解决方案。

#7

Use ABS to get the absolute value (-1 becomes 1)...

使用ABS来获得绝对值(-1变成1)…

DECLARE @Trend AS BIT
SET @Trend = 0
SELECT @Trend, ABS(@Trend-1)

#1

134