连接到Oracle中的拆分字符串列 [英] Join to splitted string columns in Oracle

问题描述

我的数据库中有一列，其中包含4个字段，以"\"分隔字符串.

I have a column in our database that holds 4 fields as a "\" delimited string.

我在报告中分别根据需要拆分了各个字段.

I have split the fields as I need them seperatly in my report.

我还需要单独使用这些字段作为针对另一个表的条件.

I also need to use these fields individually as where criteria against another table.

我尝试过的事情: 临时表:

CREATE GLOBAL TEMPORARY TABLE pfcc
   ON COMMIT PRESERVE ROWS 
   AS select REGEXP_SUBSTR(s, '[^\]+', 1, 1) colA,
       REGEXP_SUBSTR(s, '[^\]+', 1, 2) colB,
       REGEXP_SUBSTR(s, '[^\]+', 1, 3) colC,
       REGEXP_SUBSTR(s, '[^\]+', 1, 4) colD
from (select delimited s from products
                        where productid = 1)

然后将其与另一个表连接.

And then Joining this against the other table.

select * from pfcc tmp
inner join lineitems gch 
    on gch.Line = tmp.colA
    AND gch.productB = tmp.colB
    AND gch.productC = tmp.colC

我还尝试立即加入而不将值存储在表中

I also tried to join right off without storing values in a table:

select REGEXP_SUBSTR(s, '[^\]+', 1, 1) colA,
       REGEXP_SUBSTR(s, '[^\]+', 1, 2) colB,
       REGEXP_SUBSTR(s, '[^\]+', 1, 3) colC,
       REGEXP_SUBSTR(s, '[^\]+', 1, 4) colD
from (select delimited s from products
                        where productid = 1) tmp
inner join lineitems gch 
    on gch.Line = tmp.colA
    AND gch.productB = tmp.colB
    AND gch.productC = tmp.colC

我想避免使用临时表，并使其与第二种方法相似.如果不可避免，那就这样吧.

I would like to AVOID using the temporary table, and have it done similar to the second way. If this is unavoidable then so be it.

有人对此有解决方案吗?

Does anyone have a solution for this?

谢谢， JFIT

推荐答案

我认为这是您想要的查询:

I think this is the query you want:

select gch.Line, gch.productB, gch.productC,
       REGEXP_SUBSTR(p.delimited, '[^\]+', 1, 4)
from  products p inner join
      lineitems gch 
      on gch.Line = REGEXP_SUBSTR(p.delimited, '[^\]+', 1, 1) and
         gch.productB = REGEXP_SUBSTR(p.delimited, '[^\]+', 1, 2) and
         gch.productC = REGEXP_SUBSTR(p.delimited, '[^\]+', 1, 3)
where p.productid = 1;

您既不需要子查询，也不需要临时表.

You need neither a subquery nor a temporary table.

这篇关于连接到Oracle中的拆分字符串列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！