Oracle:将两个不同的查询合并为一个，LIKE &在 [英] Oracle: merging two different queries into one, LIKE & IN

问题描述

我需要实现一个搜索查询，其中我们有数据库(oracle)中单个列的多个过滤器(值).但是这些多个过滤器(值)是 LIKE 查询参数.我不确定我是否在使用头脑中的方法设想了正确的结果.

I need to implement a search query, where we have multiple filters(values) for a single column in database(oracle). But these multiple filters(values) are LIKE query parameters. I am not sure that whether I am visioning correct result using the approach in mind.

我想要的东西应该是这样的:

I want something that should work like:

departmentname IN ( LIKE '%Medi%', LIKE '%Ciga%')

我知道这行不通，只是我想展示我的愿景.

I know it will not work, just I want to show the vision I am having.

虽然我们都知道使用 foreach 并在查询之间手动添加 'OR' 很简单，例如:

Though we all know that its simple using foreach and manually adding 'OR' between queries like:

WHERE DEPARTMENTNAME LIKE '%Medi%' OR '%Ciga%' OR '%Tobacc%'

但是有没有办法同时使用 IN() 和 LIKE 来实现它?

But is there any way to implement it using IN() AND LIKE simultaneously?

也欢迎其他建议.

推荐答案

正如已经评论过的，将几个条件连接起来更好更简单:

As was already commented, it is better and simpler to just concatenate several conditions:

where departmentName like '%Medi%'
   or departmentName like '%Ciga%'
   or departmentName like '%Tabacc%';

另一种方法是将这些值 '%Medi%'、'%Ciga%' 和 '%Tabacc%' 插入一个 conditionTable，然后运行这个查询:

Another way is to insert those values '%Medi%', '%Ciga%' and '%Tabacc%' into a conditionTable, and then run this query:

select department.*
  from department
 cross join conditionTable
 where department.departmentName like conditionTable.value;

我在这里假设您的表是 department 并且 conditionTable 有一列 value.如果你实现这个解决方案，你应该关心并发性，并通过类似

I am assuming here that your table is department and that the conditionTable has a column value. If you implement this solution, you should care about concurrency, and filter conditionTable by something like

select department.*
  from department
 inner join conditionTable on conditionTable.session = yourSessionId
 where department.departmentName like conditionTable.value;

最后，如果您不想使用条件表，第三个可能很方便的解决方案是生成一个字符串 select <cond1>作为来自双联合 select <cond2> 的值from dual... 并作为

Finally, a third solution that might be handy, if you dont want to use a conditionTable, is to generate a string select <cond1> as value from dual union select <cond2> from dual... and placed into a dynamic query as

select department.*
  from department
 cross join
   (select '%Medi%' as value from dual
     union
    select '%Ciga%' from dual
     union
    select '%Tabacc%' from dual) conditionTable
 where department.departmentName like conditionTable.value;

这篇关于Oracle:将两个不同的查询合并为一个，LIKE &在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！