WHERE 子句中的 Oracle CASE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9155114/
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
Oracle CASE in WHERE clause
提问by user1191463
Can someone help me with the below query in oracle?
The logic is that if the person has a friendlyname , use that for match with the 'search' criterion. Else, try to match with the realname column.
有人可以帮助我在 oracle 中进行以下查询吗？
逻辑是，如果此人有一个 Friendlyname ，则使用它来匹配“搜索”标准。否则，尝试与实名列匹配。
select * from people where
case when customer_friendlyname is null then realname like '%abcd%'
else
case when customer_friendlyname is not null then customer_friendlyname like '%abcd%'
end
end
Appreciated if someone could take a look.. Thank you!
不胜感激，如果有人可以看看..谢谢！
回答by Bassam Mehanni
SELECT *
FROM people
WHERE (customer_friendlyname LIKE '%abc%')
OR (customer_friendlyname is null and realname LIKE '%abc%')
You actually don't need the case here, this or clause will try the friendly name first it it was null it won't match, then it will try to match using the real name
您实际上不需要这里的大小写，此 or 子句将首先尝试友好名称它为空它不会匹配，然后它将尝试使用真实名称进行匹配
回答by ruakh
In Oracle, Boolean expressions can't be treated like other types of expressions; for example, CASEexpressions can't evaluate to them. So you need to rewrite this.
在 Oracle 中，布尔表达式不能像其他类型的表达式一样对待；例如，CASE表达式不能对它们求值。所以你需要重写这个。
In this case, since you have the same LIKE '%abcd%'predicate in both branches, you couldjust factor it out:
在这种情况下，由于您LIKE '%abcd%'在两个分支中具有相同的谓词，您可以将其分解：
WHERE ( CASE WHEN customer_friendlyname IS NULL
THEN realname
ELSE customer_friendlyname
END
) LIKE '%abcd%'
but it's simpler to make use of the built-in NVLfunction, and write:
但使用内置NVL函数更简单，并编写：
WHERE NVL(customer_friendlyname, realname) LIKE '%abcd%'
回答by sergdenisov
You also can write it this way:
你也可以这样写：
select * from people where
case
when customer_friendlyname is null and realname like '%abcd%'
then 1
when customer_friendlyname is not null and customer_friendlyname like '%abcd%'
then 1
else 0
end = 1
But it is more convenient in the case when you have more expressions.
但是在表达式较多的情况下更方便。
