Oracle 如何将一个表列的子字符串等同于另一个表列中的值作为exists的一部分？

你好甲骨文大师，

我在看 “按水平连接” (https://asktom.oracle.com/pls/apex/asktom.search?tag=clever-sql) 希望我可以使用它，但似乎无法使用。也许还有另一种方法？

我们有一个产品表和一个排除表。'产品' 有名字
(其中只有子字符串形式可用作 “代码”)。
“排除” 也具有相同的代码，但是多个代码存储在用斜线分隔的同一列中。

例如:
产品:
name = 'ABC * 1.2.3.XYZ' (子字符串会将可用部分剥离出来进行比较)
名称 = '荷兰皇家航空 * 5''

排除:
代码 = 'abc/klm'
代码 = 'XYZ'
代码 = “opq/RST/UVW/efg”


有一个可怕的长而复杂的查询，拉起所有产品，除了那些被排除的 (和仅那些被排除的姐妹变体)。
我们过去只关心列表中的第一个代码。
总结起来，查询看起来像这样:

select 
from 'products p'
where 
and exists
     (select from exclusions e where 
      and ((e.code like '%/%' and subsr(e.code)  = substr(p.name))
            or
           (e.code = substr(p.name))
      )

现在需求已更改，我们希望查询在产品名称的 [substring] 与斜杠分隔的排除代码列表中的任何代码匹配时返回记录...


从头开始重写整个查询是不可能的-我上面提供的位只是最内包装的别名; 还有更多的级别它被别名，连接到其他东西，等等...
(它也是 “存在” 而不是联接的一部分，因为在代码的其他地方还有它的另一种风味
那是 “不存在”，两者应该工作相同)。

也就是说，偏好表的子集带有一些基本的where条件 (例如，今年的记录)
大约是1万条记录。


一些代码来重新创建场景:

create table products (key varchar2(5) not null, name varchar2(30) not null);
insert into products values ('AB47', 'AB345*Some name');
insert into products values ('AB47', 'AB345*Some name');
insert into products values ('AB88', 'AB345*Some name');
insert into products values ('AB47', 'AB345.A*Some name');
insert into products values ('AB22', 'AB999*Another name');

create table exclusions (code varchar2(30) not null);
insert into exclusions values ('AB345/AB999');
insert into exclusions values ('QD523');


select p.*
from products p
where p.key = 'AB47' -- AB22
and exists (select null from exclusions e where ((e.code like '%/%' and 
              substr(e.code,1,instr(e.code,'/')-1) = 
                                     substr(p.name,1,instr(p.name,'*')-1))
            or
           (e.code = substr(p.name,1,instr(p.name,'*')-1)))
      )
;

现在，键AB47返回行，但AB22不返回行，因为AB999. .. 在排除列表中排名第二。
查询应该为任一键产生a not not null结果...

您能否帮助解决如何修改查询？
我尝试了regexp_substr，但无法解决如何使用产品名称的子字符串作为模式...同样，上述页面中的 “按级别连接”，不确定如何将其循环，以便我们仅从排除项中选择一次。

只是为了使其更有趣，完整查询具有另一个表，该表将代码保留为与上述相同的格式，因此需要以相同的方式进行解析...

上面的查询基本上别名为 “q”，这样

select from 
() q,
manufacturers
where manufacturers.a = q.a and manufacturers.b = q.b ...
and  = a.code

即使当我让存在运行时，这肯定不会 ....

非常感谢!

因此，您想返回名称开头在排除值中的所有产品行？排除值可以是一个斜线分隔的值列表？

如果是这样，您所链接的技术似乎是要走的路。

在12c上，您可以使用子查询中另一个表的横向参考值。所以对于排除中的每一行，你可以为每个分离的值生成N行。

一种方法是:

select regexp_substr ( code, '[^/]+', 1, r.l ) val
from   exclusions,
       lateral (
         select level l from dual
         connect by level <= 
           length ( code ) - length ( replace ( code, '/' ) ) + 1
       ) r;

VAL     
AB345   
AB999   
QD523   

您现在需要做的就是在您的exists中引用这些内容:

with exclusion_rws as (
  select regexp_substr ( code, '[^/]+', 1, r.l ) val
  from   exclusions,
         lateral (
           select level l from dual
           connect by level <= 
             length ( code ) - length ( replace ( code, '/' ) ) + 1
         ) r
) 
select p.*
from   products p
where  exists (
   select * from exclusion_rws er
   where  er.val = substr(p.name,1,instr(p.name,'*')-1)
);

KEY    NAME                 
AB47   AB345*Some name      
AB47   AB345*Some name      
AB88   AB345*Some name      
AB22   AB999*Another name 

您现在将代码作为行，因此您也可以将它们加入制造商表中。