有没有一种优雅的方法来反转 SQL 插入语句中的位值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3324770/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there an elegant way to Invert a Bit value in an SQL insert Statement?
提问by Molloch
I'm converting some data in SQL Server:
我正在 SQL Server 中转换一些数据：
INSERT INTO MYTABLE (AllowEdit)
(Select PreventEdit from SOURCETABLE)
so I need to inverse the bit value from source table. I expected NOTto work, as this is how I would do it in code, but it doesn't. The most elegant way I can think of is:
所以我需要反转源表中的位值。我希望NOT能工作，因为这是我在代码中的做法，但事实并非如此。我能想到的最优雅的方式是：
INSERT INTO MYTABLE (AllowEdit)
(Select ABS(PreventEdit -1) from SOURCETABLE)
Is there a more standard way to do it?
有没有更标准的方法来做到这一点？
回答by driis
I did not test this myself, but you should be able to use the bitwise negation operator, ~on a bit:
我没有这个测试自己，但你应该能够使用按位反运算符，~就有点：
INSERT INTO MYTABLE (AllowEdit)
(SELECT ~PreventEdit FROM SourceTable)
回答by gbn
NOT or XORif bit
NOT 或 XOR如果位
SELECT ~PreventEdit FROM SourceTable
SELECT 1 ^ PreventEdit FROM SourceTable
If it isn't actually bit in SourceTable then this:
如果它实际上不在 SourceTable 中，那么这个：
SELECT 1 - PreventEdit FROM SourceTable
Edit: A test, note NOT is 2s complement so could give odd results if not used on a bit column
编辑：一个测试，注意 NOT 是 2s 补码，所以如果不在位列上使用可能会给出奇怪的结果
DECLARE @bitvalue bit = 1, @intvalue int = 1;
SELECT ~@bitvalue, ~@intvalue
SELECT 1 ^ @bitvalue, 1 ^ @intvalue
SELECT 1 - @bitvalue, 1 - @intvalue
SELECT @bitvalue = 0, @intvalue = 0
SELECT ~@bitvalue, ~@intvalue
SELECT 1 ^ @bitvalue, 1 ^ @intvalue
SELECT 1 - @bitvalue, 1 - @intvalue
回答by Michel MARTIN
INSERT INTO MYTABLE (AllowEdit) (SELECT ~ISNULL(PreventEdit,0) FROM SourceTable
INSERT INTO MYTABLE (AllowEdit) (SELECT ~ISNULL(PreventEdit,0) FROM SourceTable
