如何将逗号分隔的值转换为oracle中的行? [英] How to convert comma separated values to rows in oracle?

问题描述

这里是DDL-

create table tbl1 (
   id number,
   value varchar2(50)
);

insert into tbl1 values (1, 'AA, UT, BT, SK, SX');
insert into tbl1 values (2, 'AA, UT, SX');
insert into tbl1 values (3, 'UT, SK, SX, ZF');

注意，这里的值是逗号分隔字符串.

Notice, here value is comma separated string.

但是，我们需要类似以下的结果-

But, we need result like following-

ID VALUE
-------------
1  AA
1  UT
1  BT
1  SK
1  SX
2  AA
2  UT
2  SX
3  UT
3  SK
3  SX
3  ZF

我们如何为此编写SQL?

How do we write SQL for this?

推荐答案

我同意这是一个非常糟糕的设计. 如果您无法更改该设计，请尝试以下方法:

I agree that this is a really bad design. Try this if you can't change that design:

select distinct id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level
  from tbl1
   connect by regexp_substr(value, '[^,]+', 1, level) is not null
   order by id, level;

输出

id value level
1   AA  1
1   UT  2
1   BT  3
1   SK  4
1   SX  5
2   AA  1
2   UT  2
2   SX  3
3   UT  1
3   SK  2
3   SX  3
3   ZF  4

此

以更优雅，更有效的方式删除重复项(贷记@mathguy)

To remove duplicates in a more elegant and efficient way (credits to @mathguy)

select id, trim(regexp_substr(value,'[^,]+', 1, level) ) value, level
  from tbl1
   connect by regexp_substr(value, '[^,]+', 1, level) is not null
      and PRIOR id =  id 
      and PRIOR SYS_GUID() is not null  
   order by id, level;

如果您希望采用"ANSIer"方法，请使用CTE:

If you want an "ANSIer" approach go with a CTE:

with t (id,res,val,lev) as (
           select id, trim(regexp_substr(value,'[^,]+', 1, 1 )) res, value as val, 1 as lev
             from tbl1
            where regexp_substr(value, '[^,]+', 1, 1) is not null
            union all           
            select id, trim(regexp_substr(val,'[^,]+', 1, lev+1) ) res, val, lev+1 as lev
              from t
              where regexp_substr(val, '[^,]+', 1, lev+1) is not null
              )
select id, res,lev
  from t
order by id, lev;

输出

id  val lev
1   AA  1
1   UT  2
1   BT  3
1   SK  4
1   SX  5
2   AA  1
2   UT  2
2   SX  3
3   UT  1
3   SK  2
3   SX  3
3   ZF  4

MT0的另一种递归方法，但不使用正则表达式:

Another recursive approach by MT0 but without regex:

WITH t ( id, value, start_pos, end_pos ) AS
  ( SELECT id, value, 1, INSTR( value, ',' ) FROM tbl1
  UNION ALL
  SELECT id,
    value,
    end_pos                    + 1,
    INSTR( value, ',', end_pos + 1 )
  FROM t
  WHERE end_pos > 0
  )
SELECT id,
  SUBSTR( value, start_pos, DECODE( end_pos, 0, LENGTH( value ) + 1, end_pos ) - start_pos ) AS value
FROM t
ORDER BY id,
  start_pos;

我尝试了3种方法，这些方法具有30000行数据集并返回118104行，并得到以下平均结果:

I've tried 3 approaches with a 30000 rows dataset and 118104 rows returned and got the following average results:

• 我的递归方法:5秒
• MT0方法:4秒
• 数学方法:16秒
• MT0递归方法无正则表达式:3.45秒

@Mathguy还使用更大的数据集进行了测试:

@Mathguy has also tested with a bigger dataset:

在所有情况下，递归查询(我只对常规查询进行了测试 substr和instr)的效果要好2到5倍.这是 每个字符串的字符串/令牌数量和CTAS执行的组合 分层与递归的时间，分层优先.所有时间 秒

In all cases the recursive query (I only tested the one with regular substr and instr) does better, by a factor of 2 to 5. Here are the combinations of # of strings / tokens per string and CTAS execution times for hierarchical vs. recursive, hierarchical first. All times in seconds

• 30,000 x 4:5/1
• 30,000 x 10:15/3.
• 30,000 x 25:56/37.
• 5,000 x 50:33/14.
• 5,000 x 100:160/81.
• 10,000 x 200:1,924/772
• 30,000 x 4: 5 / 1.
• 30,000 x 10: 15 / 3.
• 30,000 x 25: 56 / 37.
• 5,000 x 50: 33 / 14.
• 5,000 x 100: 160 / 81.
• 10,000 x 200: 1,924 / 772

这篇关于如何将逗号分隔的值转换为oracle中的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！