oracle sql中条件条件的动态数 [英] dynamic number of where condition in oracle sql
问题描述
我需要为报表工具中的提示编写一个sql.我在用'-'分隔的变量中获得了多个值的列表,并且这些值的数量可以变化.(例如,1."abc-def" eg2."abc-def-xyz").
I need to write a sql for a prompt in a reporting tool. I get the list of multiple values in a variable separated by '-' and the number of such values can vary.(eg1."abc-def" eg2."abc-def-xyz").
现在,我需要在逻辑上使用这种形式的oracle编写sql
Now I need to write sql in oracle of this form(logically)
select something
from somewhere
where someColumn in 'abc' -- use substr to extract abc
or someColumn in 'def' -- use substr to extract def
or ...till we don't find '-'.
我无法使用plsql进行查询.我可能不知道要使用我在plsql中选择的变量,或者可能是报表工具不支持该变量.
I can't use plsql for the query. Either I may not be knowing to use the variable in which I select into in plsql or may be the reporting tool doesn't support that.
谢谢.
推荐答案
尝试
select something
from somewhere
where someColumn in (select regexp_substr('abc-def-xyz','[^-]+', 1, level) from dual
connect by regexp_substr('abc-def-xyz', '[^-]+', 1, level) is not null);
概括(考虑您的字段以-"分隔)
To generalize (considering your fields are separated by "-")
select something
from somewhere
where someColumn in (select regexp_substr(variable,'[^-]+', 1, level) from dual
connect by regexp_substr(variable, '[^-]+', 1, level) is not null);
基本上,子查询的输出如下所示-
Basically the output of the subquery is shown below -
SQL> select regexp_substr('abc-def-xyz','[^-]+', 1, level) value from dual
connect by regexp_substr('abc-def-xyz', '[^-]+', 1, level) is not null;
VALUE
--------------------------------
abc
def
xyz
这篇关于oracle sql中条件条件的动态数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!