布尔’非’在 T-SQL 不工作’位’数据类型？

尝试执行单个布尔 NOT 操作时，似乎在 MSSQLServer2005下面的代码块不起作用

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

相反，我越来越成功

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

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

我错过了什么吗？

89636 次浏览
小开

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

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

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.

小开

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:

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.

小开
最佳答案

Use the ~ operator:

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

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

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)

The best way to think of bit fields in logic is as an integer that's 1 or 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.

小开

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

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

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.

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

提交答案