Oracle SQL如何对存储逗号分隔字符串的列实现多值搜索？

假设你已经将多值入参:PX_ITEM通过正则拆分生成了单行单搜索值的结果集，拆分SQL通用写法如下：

select regexp_substr(:PX_ITEM, '[^,]+', 1, level) search_val
from dual
connect by regexp_substr(:PX_ITEM, '[^,]+', 1, level) is not null

根据不同的搜索需求，对应实现方式如下：

• 需求1：匹配任意搜索值即可返回结果（最常用场景）
  推荐使用EXISTS关联实现，不会产生重复行，性能更优：

  select t.*
  from 你的业务表 t
  where exists (
      select 1
      from (
          select regexp_substr(:PX_ITEM, '[^,]+', 1, level) search_val
          from dual
          connect by regexp_substr(:PX_ITEM, '[^,]+', 1, level) is not null
      ) s
      where instr(',' || t.存储逗号分隔值的列名 || ',', ',' || s.search_val || ',') > 0
  )
  

  注：列值和搜索值前后拼接逗号是为了避免部分匹配，例如搜索1不会误匹配到存储值11,2

• 需求2：必须匹配所有搜索值才返回结果
  通过分组计数实现：

  select t.*
  from 你的业务表 t
  join (
      select regexp_substr(:PX_ITEM, '[^,]+', 1, level) search_val
      from dual
      connect by regexp_substr(:PX_ITEM, '[^,]+', 1, level) is not null
  ) s on instr(',' || t.存储逗号分隔值的列名 || ',', ',' || s.search_val || ',') > 0
  group by t.主键列, t.所有需要返回的列
  having count(distinct s.search_val) = (
      select count(*)
      from (
          select regexp_substr(:PX_ITEM, '[^,]+', 1, level) search_val
          from dual
          connect by regexp_substr(:PX_ITEM, '[^,]+', 1, level) is not null
      )
  )
  

APEX 适配注意事项 #

• 如果使用APEX 22.1及以上版本，Popup LOV原生支持多值返回，上述SQL可以直接作为LOV的源查询使用，无需额外参数处理
• 不要直接使用column in (:PX_ITEM)写法，绑定变量传入的逗号分隔字符串会被识别为单个值，无法触发多值匹配
• 如果存储的逗号分隔值包含空格，关联时可以搭配trim()函数处理搜索值和列值，避免匹配失败

内容的提问来源于stack exchange，提问作者Goku