SQL - 对一列进行逆透视结果

Question

在 Oracle 10g 上，假设我有以下列：

col
------------------------------------------------------------------------------------------------
[1,98]([1,81]([6,100828],[6,101260]),[1,81]([6,100529],[6,101259]),[1,81]([6,101709],[6,100474]))

并且我想显示此结果：

col
------
100828
101260
100529
101259
101709
100474

是否可以使用 SQL 查询显示此结果？

实际上我尝试过：

SELECT SUBSTR(col, INSTR(col, ',', 1, 3) + 1, 6) exp_1,
       SUBSTR(col, INSTR(col, ',', 1, 5) + 1, 6) exp_2,
       SUBSTR(col, INSTR(col, ',', 1, 8) + 1, 6) exp_3,
       SUBSTR(col, INSTR(col, ',', 1, 10) + 1, 6) exp_4,
       SUBSTR(col, INSTR(col, ',', 1, 13) + 1, 6) exp_5,
       SUBSTR(col, INSTR(col, ',', 1, 15) + 1, 6) exp_6
  FROM (SELECT '[1,98]([1,81]([6,100828],[6,101260]),[1,81]([6,100529],[6,101259]),[1,81]([6,101709],[6,100474]))' col
          FROM dual) ; 

EXP_1  EXP_2  EXP_3  EXP_4  EXP_5  EXP_6
------ ------ ------ ------ ------ ------
100828 101260 100529 101259 101709 100474

但是，返回的 exp_% 的数量可能是可变的并且总是成对的，这意味着另一行可以返回 8 exp_% ：

SUBSTR(col, INSTR(col, ',', 1, 18) + 1, 6) exp_7 ,
SUBSTR(col, INSTR(col, ',', 1, 20) + 1, 6) exp_8

当 exp_% 的数量固定时的建议也非常受欢迎！

谢谢。

Answer 1

假设您的表名为“foo”，列名为“col”：

with q as (
    select ','||regexp_replace(
        regexp_replace(
            regexp_replace(
                regexp_replace(col, '[[0-9,]*]\(', ''), 
                    '\[[0-9],', ''), 
                '[])]', ','), 
        ',,+', 
        ',') a from foo
) 
select data 
  from (select substr(a, instr(a, ',', 1, rownum) + 1, 6) data 
          from q,
               (select 1 from q connect by level < length(regexp_replace(a, '[0-9]', '')))
       )
;

以下是解释。这很快就会变得复杂，并且可能无法很好地扩展，因此买家要小心。

首先，我想删除 '[1,98](' 字段。

  1  with q as (
  2  select
  3          regexp_replace(col, '[[0-9,]*]\(', '')
  4  from foo
  5  )
  6* select * from q

REGEXP_REPLACE(COL,'[[0-9,]*]\(','')
------------------------------------------------------------------------------------------------------------------------------------
[6,100828],[6,101260])[6,100529],[6,101259])[6,101709],[6,100474]))

接下来，我想删除字段的 '[n,' 部分。

  1  with q as (
  2  select
  3      regexp_replace(
  4          regexp_replace(col, '[[0-9,]*]\(', ''),
  5          '\[[0-9],', ''
  6      ) a from foo
  7  )
  8* select * from q

A
------------------------------------------------------------------------------------------------------------------------------------
100828],101260])100529],101259])101709],100474]))

现在删除所有 ']' 和 ')'

  1  with q as (
  2  select
  3      regexp_replace(
  4      regexp_replace(
  5          regexp_replace(col, '[[0-9,]*]\(', ''),
  6          '\[[0-9],', ''),
  7          '[])]', ',')
  8       a from foo
  9  )
 10* select * from q

A
------------------------------------------------------------------------------------------------------------------------------------
100828,,101260,,100529,,101259,,101709,,100474,,,

删除重复的逗号并在前面加上逗号。

  1  with q as (
  2  select ','||regexp_replace(
  3      regexp_replace(
  4      regexp_replace(
  5          regexp_replace(col, '[[0-9,]*]\(', ''),
  6          '\[[0-9],', ''),
  7          '[])]', ','),
  8      ',,+',
  9      ',') a from foo
 10  )
 11* select * from q

A
------------------------------------------------------------------------------------------------------------------------------------
,100828,101260,100529,101259,101709,100474,

找出它们有多少个字段并为每个字段创建一行。

  1  with q as (
  2  select ','||regexp_replace(
  3      regexp_replace(
  4      regexp_replace(
  5          regexp_replace(col, '[[0-9,]*]\(', ''),
  6          '\[[0-9],', ''),
  7          '[])]', ','),
  8      ',,+',
  9      ',') a from foo
 10  )
 11* select 1 from q connect by level < length(regexp_replace(a, '[0-9]', ''))

     1
----------
     1
     1
     1
     1
     1
     1
     1

使用 q 进行笛卡尔连接（请注意，如果表中有多于一行，则这将不起作用。）和一个子字符串以获得最终答案。

  1  with q as (
  2  select ','||regexp_replace(
  3      regexp_replace(
  4      regexp_replace(
  5          regexp_replace(col, '[[0-9,]*]\(', ''),
  6          '\[[0-9],', ''),
  7          '[])]', ','),
  8      ',,+',
  9      ',') a from foo
 10  )
 11  select data
 12    from (select substr(a, instr(a, ',', 1, rownum) + 1, 6) data
 13        from q,
 14         (select 1 from q connect by level < length(regexp_replace(a, '[0-9]', '')))
 15*        )

DATA
------
100828
101260
100529
101259
101709
100474


6 rows selected.