带过滤器的 oracle 条件 where 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13951711/
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 conditional where clause with filter
提问by serefbilge
I have to use a conditional where clause like below one, what is the working version of this clause?
我必须使用如下所示的条件 where 子句，该子句的工作版本是什么？
AND CASE WHEN b.kurum_turu = 1
THEN u.kod in ('1125', '2975', '1127', '4460', '1128', '1126')
ELSE u.kod in ('1125', '2975')
END
回答by Taryn
Sounds like you want this for a WHEREclause:
听起来你想要一个WHERE条款：
WHERE
(
b.kurum_turu = 1
AND u.kod in ('1125', '2975', '1127', '4460', '1128', '1126')
)
OR
(
u.kod in ('1125', '2975')
)
回答by Vikdor
You can rewrite that as
您可以将其重写为
...
WHERE
(
(b.kurum_turu = 1 AND u.kod in ('1125', '2975', '1127', '4460', '1128', '1126'))
OR
((b.kurum_turu IS NULL OR b.kurum_turu != 1) AND u.kod in ('1125', '2975'))
)
回答by GKV
i think this is what you need..
我想这就是你需要的..
where ...AND CASE WHEN b.kurum_turu = 1
and u.kod in ('1125', '2975', '1127', '4460', '1128', '1126') then 1
when u.kod in ('1125', '2975') then 1
else 0
END=1
回答by gaston guido
My aproach is this:
我的方法是这样的：
AND 1 = CASE
WHEN b.kurum_turu = 1 AND u.kod in ('1125', '2975', '1127', '4460', '1128', '1126') THEN 1 ELSE 1 END
回答by My-Name-Is
Transform a list of strings by using CONNECT BYin order to obtain a conditional INstatement.
通过使用转换字符串列表CONNECT BY以获得条件IN语句。
This solution is usefull if you have serveral conditionals since you can write the INclause as a single expression without any other AND, OR restrictions.
如果您有多个条件，则此解决方案很有用，因为您可以将IN子句编写为单个表达式，而没有任何其他 AND、OR 限制。
WHERE u.kod IN
(
SELECT rowdata FROM
( WITH DATA AS
(SELECT CASE WHEN b.kurum_turu = 1 THEN '1125, 2975, 1127, 4460, 1128, 1126' ELSE '1125, 2975' END rowdata FROM dual )
SELECT trim(regexp_substr(rowdata, '[^,]+', 1, LEVEL)) rowdata FROM DATA CONNECT BY instr(rowdata, ',', 1, LEVEL - 1) > 0
)
)
This comes with the drawback that the transformation of the comma separated values to data rows is a bit complex ... but you can extract this complex part into a well named function in order to hide this complexity.
这样做的缺点是将逗号分隔的值转换为数据行有点复杂……但是您可以将这个复杂的部分提取到一个命名良好的函数中，以隐藏这种复杂性。
