主页 / dba / 问题 / 39002
Accepted
a1ex07
Asked: 2013-04-02 09:55:42 +0800 CST

使用 Oracle 分层查询将字段值转换为行

  • 772

我表中的一列存储以逗号分隔的值列表（我知道这是一个糟糕的设计，但目前不能进行重构）。我似乎想出了一个可行的解决方案，但恐怕效率不高：

select distinct test_id , regexp_substr(str_data,'[^,]+', 1, level)  
from 
(
select 1 as test_id, '1,2,3' as str_data from dual 
union all 
select 2 as test_id, '4,5,6' as str_data from dual
)test_data 
connect 
by 
regexp_substr(str_data, '[^,]+', 1, level) is not null

我不喜欢我必须使用distinct. 有更好的方法吗？

谢谢你。

oracle hierarchy

1 个回答

  1. Best Answer

    您CONNECT BY将每一行加入其他（包括它自己）N 次。这是一个多自动交叉连接。我可以看到当记录数量增加时它在性能方面的表现如何。

    我建议改为这样的查询：

    SQL> SELECT test_id, regexp_substr(str_data, '[^,]+', 1, lvl)
  2    FROM (SELECT 1 AS test_id, '1,2,3' AS str_data
  3            FROM dual
  4          UNION ALL
  5          SELECT 2 AS test_id, '4,5,6' AS str_data FROM dual) test_data
  6    JOIN (SELECT LEVEL lvl FROM dual CONNECT BY LEVEL <= 1000)
  7      ON lvl <= length(str_data) - length(REPLACE(str_data, ',')) + 1;

TEST_ID REGEXP_SUBSTR(STR_DATA,'[^,]+',1,LVL)
------- -------------------------------------
      1                                     1
      2                                     4
      1                                     2
      2                                     5
      1                                     3
      2                                     6

    将 1000 替换为最大元素数（或使用内部COUNT子查询计算）。每行只会被查询 n 次（其中 n 是逗号的数量）。

    此外，如果性能至关重要，请使用INSTRandSUBSTR而不是REGEX.

    • 2

相关问题