SQL INSERT（选择）使用基于另一个列值的 CASE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7079124/
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
SQL INSERT (select) using CASE based on another columns value
提问by Tom
I have an alarm trigger, and if that alarm trigger = 1, I want the value of another column to be NULL. For examples purposes, I'll create a simple table of what I'm trying to explain.
我有一个警报触发器，如果该警报触发器 = 1，我希望另一列的值为 NULL。出于示例目的，我将创建一个简单的表格来说明我要解释的内容。
DECLARE @tmp TABLE ( ID INT, Value1 FLOAT, V1_Alarm BIT, Value2 FLOAT, V2_Alarm BIT)
INSERT INTO @tmp
SELECT (
ID,
CASE WHEN V1_Alarm = 1 THEN NULL ELSE Value1,
V1_Alarm,
CASE WHEN V2_Alarm = 1 THEN NULL ELSE Value2
) FROM SomeTable
回答by James Hill
You're not ending your case statements properly. Also, the parentheses are unnecessary and prevent multiple values from being passed. Here's your sample code modified:
你没有正确结束你的案例陈述。此外，括号是不必要的，可以防止传递多个值。这是您修改的示例代码：
DECLARE @tmp TABLE ( ID INT, Value1 FLOAT, V1_Alarm BIT, Value2 FLOAT, V2_Alarm BIT)
INSERT INTO @tmp
SELECT ID,
CASE WHEN V1_Alarm = 1 THEN
NULL
ELSE
Value1
END,
V1_Alarm,
CASE WHEN V2_Alarm = 1 THEN
NULL
ELSE
Value2
END
FROM SomeTable
