2021-01-29 Oracle SQL实现动态设置查询条件

之前遇到一个需求：在查询某个表（A）的数据时需要按照查询规则来查询，就是在另一个表（B）配置一条规则记录，按照这条记录的设置来查询A表符合条件的数据， B表记录规则如下，数据为多个查询条件之间用‘ ’空格来连接，空格连接的是多个关键词，A表中的数据只要有B表该记录的任意一条关键词就符合查询条件，‘%’ 百分号连接的关键词是需要A表数据既要包含前者关键词也要包含后者关键词。

简化后的表设计：

A表字段 （title varchar2（100），desc varchar2(1000) )

B表字段 （type varchar2（20），rule varchar2(500) )

举例 B表规则记录为 aaa bbb ccc%ddd ,如果规则是这样，就需要查询A表的desc字段包含aaa或者包含bbb 或者同时包含 ccc和ddd的数据，此时的查询条件就是 (A.desc like '%aaa%') or (A.desc like '%bbb%') or A.desc like '%ccc%ddd%'

面对以上需求，首先第一步需要考虑先将B表的该条配置规则查出来，并且按照空格进行拆分成多条记录，然后再将A表记录和拆分好的记录进行连接，根据连接后的配置规则条件进行查询。

• 1. 按照空格拆分
     这里就用到了oracle提供的sql拆分函数 regexp_substr()方法, 使用该方法将 配置的规则记录拆分成多条规则记录。

REGEXP_SUBSTR方法介绍

function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
string:需要进行正则处理的字符串
pattern：进行匹配的正则表达式
position：起始位置，从字符串的第几个字符开始正则表达式匹配（默认为1） 注意：字符串最初的位置是1而不是0
occurrence：获取第几个分割出来的组（分割后最初的字符串会按分割的顺序排列成组）
modifier：模式（‘i’不区分大小写进行检索；‘c’区分大小写进行检索。默认为’c’）针对的是正则表达式里字符大小写的匹配
上述情况的函数为 regexp_substr('aa bb cc%dd','[^ ]+',1,1,'c')

• 2.递归查询出按照空格拆分的所有记录

regexp_substr('aa bb cc%dd','[^ ]+',1,1,'c') 这样查出来的数据只有aa一条，因为occurrence 只能指定查询拆分的结果第几组，要想将拆分的结果都查出来还需要递归查询出拆分的所有数据，因此需要使用connect by 来进行递归,需要使用level这个伪列来指定每次不同分组的数据，还需要知道一共拆分成了多少组数据，需要使用regexp_count()方法来求出拆分的数量。

REGEXP_COUNT函数说明

Oracle的11g引入此函数
REGEXP_COUNT ( source_char, pattern , position , match_param)
REGEXP_COUNT 返回pattern 在source_char 串中出现的次数。如果未找到匹配，则函数返回0。position 变量告诉Oracle 在源串的什么位置开始搜索。在开始位置之后每出现一次模式，都会使计数结果增加1。
match_param 变量支持下面几个值：
‘i’ 用于不区分大小写的匹配
‘c’ 用于区分大小写的匹配
‘n’ 允许句点(.)作为通配符去匹配换行符。如果省略该参数，则句点将不匹配换行符
‘m’ 将源串视为多行。即Oracle 将^和$分别看作源串中任意位置任何行的开始和结束，而不是仅仅看作整个源串的开始或结束。如果省略该参数，则Oracle将源串看作一行。
‘x’ 忽略空格字符。默认情况下，空格字符与自身相匹配。

• 3. 拆分成多条记录
     按照需求组织的sql为：

 select regexp_substr('aa bb cc%dd' ,'[^ ]+' ,1 level ) from dual connect by level <= regexp_count('aa bb cc%dd',' ')+1 
 -- 利用递归查出拆分的数据，level从1开始，每一次递归，leval都会+1，因此使用level来指定每一次要取的拆分的第几个分组的数据，regexp_count来计算出包含的空格数量，
-- 拆分的数据个数应该是空格数量+1，因此递归的条件为 level <= regexp_count('aa bb cc%dd',' ')+1 
-- 因为配置规则应该是从表里查出来的，所以应该将 ‘aa bb cc%dd’替换成查询数据，
-- 假设要查询的数据是B表的type值为‘r001’的记录的rule字段，所以查询语句应该是 select rule from B where type='r001'

-- 因此 将规则拆分成多条记录的sql为
select regexp_substr( ( select rule from B where type='r001' )  ,'[^ ]+' ,1 level ) value
from dual connect by level <= regexp_count( (select rule from B where type='r001') ,' ')+1 

• 4. 组织查询条件

select title,desc from A , ( 
select regexp_substr( ( select rule from B where type='r001' )  ,'[^ ]+' ,1 level ) value from dual connect by level <= regexp_count( (select rule from B where type='r001') ,' ')+1
 )C where A.desc like '%'|| C.value ||'%' 
 -- 这样查询正好可以查出A表的desc字段中包含B表配置规则 按照空格拆分的内容，如果是拆到的某条记录是用%
连接的，正好可以和前后的‘%’拼接起来，就可以实现 ‘%’连接的关键词要求desc字段中都包含了。
-- 比如按照空格拆分后 某条记录为 ‘cc%dd’，那么在最后的查询条件里就会拼接成 A.desc like '%cc%dd%',这样就正好实现了 ‘%’连接的关键词要求某个字段必须都包含的需求。

--但是这样还会出现一个问题，就是查出来的记录是会存在重复数据，因为用A表的所有记录都与拆分的规则记录进行了连接，所以同一条A表记录如果同时符合多条规则就有可能出现重复的A表数据，所以此时就需要进行去除重复数据
--此时有多种办法去重，可以加关键字 distinct ，也可以在进行分组查询去查询每个分组中的一条记录，可以是最大值，也可以是最小值等等..此处就不再进行组织sql了

本文关于sql 字符串拆分参考过其他文章，原文地址https://blog.youkuaiyun.com/bw555/article/details/41986339