“通过...连接"从多个定界字符串生成行 [英] "Connect By" to generate rows from multiple delimited string

问题描述

我们可以使用"Connect By"从oracle中的分隔字符串生成行.像:

SELECT Rn ,Regexp_Substr(data, '[^,]+', 1, LEVEL) Data
FROM (SELECT 1 Rn ,'id:a,val:b,desc:c' data FROM Dual) Idata
CONNECT BY Regexp_Substr(data, '[^,]+', 1, LEVEL) IS NOT NULL

我想将内部查询用作更多其他所有记录的并集.像这样:

SELECT Rn ,Regexp_Substr(data, '[^,]+', 1, LEVEL) Data
    FROM (SELECT 1 Rn ,'id:a,val:b,desc:c' data FROM Dual
      UNION ALL
      SELECT 2 Rn ,'id:a2,val:b2,desc:c2' data FROM Dual
      UNION ALL
      SELECT 3 Rn ,'id:a3,val:b3,desc:c3' data FROM Dual) Idata
    CONNECT BY Regexp_Substr(data, '[^,]+', 1, LEVEL) IS NOT NULL;

这样我就可以得到一个结果集，

RN  DATA
1   desc:c
1   id:a
1   val:b
2   desc:c2
2   id:a2
2   val:b2
3   desc:c3
3   id:a3
3   val:b3

但是它不能正常工作，它以:

的形式出现

RN  DATA
1   desc:c
1   desc:c
1   desc:c
1   desc:c
1   desc:c
1   desc:c
1   desc:c
1   desc:c
1   desc:c
1   id:a
1   val:b
1   val:b
1   val:b
2   desc:c2
2   desc:c2
2   desc:c2
2   desc:c2
2   desc:c2
2   desc:c2
2   desc:c2
2   desc:c2
:
:
:

应用DISTINCT不是目标.因为字符串可能会有所不同，在这里需要花费大量时间才能拆分成更大的字符串.我猜，在此查询中，LEVEL生成不正确.可能是，可能需要使用超过Rn的按设施分组.有人可以帮我写这个查询吗?谢谢你提前. :)

解决方案

如果您使用的是11gR2，则可以使用RCTE:

with Idata as
(SELECT 1 Rn ,'id:a,val:b,desc:c' data FROM Dual
      UNION ALL
      SELECT 2 Rn ,'id:a2,val:b2,desc:c2' data FROM Dual
      UNION ALL
      SELECT 3 Rn ,'id:a3,val:b3,desc:c3' data FROM Dual),
rcte(rn, txt, token, i) as
(
  select rn, data, Regexp_Substr(data, '[^,]+', 1, 1), 2
  from Idata
  union all
  select rn, txt, Regexp_Substr(txt, '[^,]+', 1, i), i+1
  from rcte 
  where Regexp_Substr(txt, '[^,]+', 1, i) IS NOT null
  )
select rn, token
from rcte
order by rn;

如果没有，并且添加DISTINCT非常繁琐，那么您可以尝试其他方法，例如使用管道函数-

create or replace type t is object(token varchar2(100));
/
create or replace type t_tab as table of t;
/
create or replace function split_string(str varchar2, del in varchar2) return t_tab
  pipelined is

  token    varchar2(4000);
  str_t   varchar2(4000) ;
  v_del_i number;


begin

    str_t := str;

    while str_t is not null loop

      v_del_i := instr(str_t, del, 1, 1);

      if v_del_i = 0 then
        token  := str_t;
        str_t := '';
      else
        token  := substr(str_t, 1, v_del_i - 1);
        str_t := substr(str_t, v_del_i + 1);
      end if;

      pipe row(t(token));

    end loop;

 return;
end split_string;
/

现在查询看起来像这样:

select t.token, Idata.rn
from (SELECT 1 Rn ,'id:a,val:b,desc:c' data FROM Dual
      UNION ALL
      SELECT 2 Rn ,'id:a2,val:b2,desc:c2' data FROM Dual
      UNION ALL
      SELECT 3 Rn ,'id:a3,val:b3,desc:c3' data FROM Dual) Idata ,
      table(split_string(Idata.data, ',')) t 

这是一个sqlfiddle演示

we can use "Connect By" to generate rows from a delimited string in oracle. like:

SELECT Rn ,Regexp_Substr(data, '[^,]+', 1, LEVEL) Data
FROM (SELECT 1 Rn ,'id:a,val:b,desc:c' data FROM Dual) Idata
CONNECT BY Regexp_Substr(data, '[^,]+', 1, LEVEL) IS NOT NULL

I want to use the inner query as a union all of a few more records. Something like:

SELECT Rn ,Regexp_Substr(data, '[^,]+', 1, LEVEL) Data
    FROM (SELECT 1 Rn ,'id:a,val:b,desc:c' data FROM Dual
      UNION ALL
      SELECT 2 Rn ,'id:a2,val:b2,desc:c2' data FROM Dual
      UNION ALL
      SELECT 3 Rn ,'id:a3,val:b3,desc:c3' data FROM Dual) Idata
    CONNECT BY Regexp_Substr(data, '[^,]+', 1, LEVEL) IS NOT NULL;

So that I could get a result set as,

RN  DATA
1   desc:c
1   id:a
1   val:b
2   desc:c2
2   id:a2
2   val:b2
3   desc:c3
3   id:a3
3   val:b3

But it is not working properly, it is coming as :

RN  DATA
1   desc:c
1   desc:c
1   desc:c
1   desc:c
1   desc:c
1   desc:c
1   desc:c
1   desc:c
1   desc:c
1   id:a
1   val:b
1   val:b
1   val:b
2   desc:c2
2   desc:c2
2   desc:c2
2   desc:c2
2   desc:c2
2   desc:c2
2   desc:c2
2   desc:c2
:
:
:

Applying DISTINCT is not the target. because the strings could be different and here it is taking huge time to split for bigger strings. Something LEVEL generation is not proper, I guess, in this query. May be, group by facility over Rn may need to be used. Can any body help me out to write this query? Thanks n advance. :)

解决方案

If you're using 11gR2 you can use RCTE:

with Idata as
(SELECT 1 Rn ,'id:a,val:b,desc:c' data FROM Dual
      UNION ALL
      SELECT 2 Rn ,'id:a2,val:b2,desc:c2' data FROM Dual
      UNION ALL
      SELECT 3 Rn ,'id:a3,val:b3,desc:c3' data FROM Dual),
rcte(rn, txt, token, i) as
(
  select rn, data, Regexp_Substr(data, '[^,]+', 1, 1), 2
  from Idata
  union all
  select rn, txt, Regexp_Substr(txt, '[^,]+', 1, i), i+1
  from rcte 
  where Regexp_Substr(txt, '[^,]+', 1, i) IS NOT null
  )
select rn, token
from rcte
order by rn;

If not, and adding DISTINCT is to heavy, then you can try a different approach such as using a pipelined function-

create or replace type t is object(token varchar2(100));
/
create or replace type t_tab as table of t;
/
create or replace function split_string(str varchar2, del in varchar2) return t_tab
  pipelined is

  token    varchar2(4000);
  str_t   varchar2(4000) ;
  v_del_i number;


begin

    str_t := str;

    while str_t is not null loop

      v_del_i := instr(str_t, del, 1, 1);

      if v_del_i = 0 then
        token  := str_t;
        str_t := '';
      else
        token  := substr(str_t, 1, v_del_i - 1);
        str_t := substr(str_t, v_del_i + 1);
      end if;

      pipe row(t(token));

    end loop;

 return;
end split_string;
/

Now the query could look like this:

select t.token, Idata.rn
from (SELECT 1 Rn ,'id:a,val:b,desc:c' data FROM Dual
      UNION ALL
      SELECT 2 Rn ,'id:a2,val:b2,desc:c2' data FROM Dual
      UNION ALL
      SELECT 3 Rn ,'id:a3,val:b3,desc:c3' data FROM Dual) Idata ,
      table(split_string(Idata.data, ',')) t 

Here is a sqlfiddle demo

这篇关于“通过...连接"从多个定界字符串生成行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！