SQL 如何在带有 IS NULL 的 WHERE 子句中使用 CASE 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6867917/
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
How can I use a CASE statement in a WHERE clause with IS NULL?
提问by codea
Here's my queries, they don't work but I want to do something like this :
这是我的查询，它们不起作用，但我想做这样的事情：
SELECT a_field FROM a_table
WHERE
...
AND
CASE
WHEN a_value_from_another_query IS NULL THEN a_second_field IS NULL
ELSE a_second_field = a_value_from_another_query
END
Or
或者
SELECT a_field FROM a_table
WHERE
...
AND
CASE a_value_from_another_query
WHEN NULL THEN a_second_field IS NULL
ELSE a_second_field = a_value_from_another_query
END
Or
或者
SELECT a_field FROM a_table
WHERE
...
AND
CASE NVL(a_value_from_another_query, 'x')
WHEN 'x' THEN a_second_field IS NULL
ELSE a_second_field = a_value_from_another_query
END
When a_value_from_another_query IS NULL, I want to add a_second_field IS NULLto my WHERE clause, when a_value_from_another_query IS NOT NULL, I want to add a_second_field = a_value_from_another_queryto my WHERE clause. How can I achieve this ?
当a_value_from_another_query IS NULL，我想添加a_second_field IS NULL到我的 WHERE 子句中，当a_value_from_another_query IS NOT NULL，我想添加a_second_field = a_value_from_another_query到我的 WHERE 子句中。我怎样才能做到这一点？
回答by staticsan
Sounds like you simply picked up the wrong tool from the toolbox.
听起来您只是从工具箱中选择了错误的工具。
Unless I have horribly misunderstood you, the following:
除非我严重误解了您，否则以下内容：
WHERE
(a_value_from_another_query IS NULL AND a_second_field IS NULL)
OR
(a_value_from_another_query IS NOT NULL AND a_second_field = a_value_from_another_query)
... should so what you want.
...应该是你想要的。
回答by Aitor
There are two ways to use a CASE statement:
有两种使用 CASE 语句的方法：
1. CASE WHEN condition_1 THEN return_expr_1
[WHEN condition_2 THEN return_expr_2 ….]
[WHEN condition_n THEN return_expr_n ….]
[ELSE default] END
2. CASE expression WHEN value1 THEN result1
[WHEN value2 THEN result2
.....
ELSE resultn
]
END
In your selects, you are using instead a result, another expression. This isn't going to work. If you want to get your query working, you have to use the first case expression, and return a value, something like this:
在您的选择中，您使用的是结果，另一个表达式。这行不通。如果你想让你的查询工作，你必须使用第一个 case 表达式，并返回一个值，如下所示：
SELECT a_field FROM a_table
WHERE
...
AND nvl(a_second_field,'x')=(CASE WHEN a_value_from_another_query IS NULL THEN 'X'
ELSE a_value_from_another_query END)
