主页 / dba / 问题 / 324220
Accepted
Pantea
Asked: 2023-03-01 21:02:55 +0800 CST

如何将具有“管道分隔”值的字符串转换为 Oracle PL/SQL 中的单独行

  • 772

我有一个具有以下结构的表：

create table student_info
(
  item_number    number,
  st_firstname   varchar2(50),
  st_lastname    varchar2(50),
  st_score       varchar2(50)
)

这是表的示例数据：

item_number  |  st_firstname   |         st_lastname      |  st_score
----------------------------------------------------------------------------
     1         Ali|Reza|Pantea    Hashemi|Nosrati|Yaghobi    10|20|20
     
     2          Maryam|Ahmad         Moghise|Majlesi          20|20

我需要有以下输出：

item_number  |  st_firstname   |         st_lastname      |  st_score
----------------------------------------------------------------------------
     1             Ali                    Hashemi               10
     1             Reza                   Nosrati               20
     1             Pantea                 Yaghobi               20
     
     2             Maryam                 Moghise               20
     2             Ahmad                  Majlesi               20

我发现通过以下查询，我可以使用其中一列（即st_firstname）做我想做的事：

select distinct t.item_number,
                trim(regexp_substr(t.st_firstname, '[^|]+', 1, level)) str
  from student_info t
connect by instr(st_firstname, '|', 1, level - 1) > 0
 order by t.item_number

问题是我不知道如何将其他列（st_lastname，st_lastname）添加到上述查询中。我想知道你是否可以在这里帮助我。

提前致谢

oracle

1 个回答

  1. Best Answer

    您只需要添加额外的列，例如：

    WITH student_info AS (SELECT 1 item_number, 'Ali|Reza|Pantea' st_firstname, 'Hashemi|Nosrati|Yaghobi' st_lastname, '10|20|20' st_score FROM dual UNION ALL
                      SELECT 2 item_number, 'Maryam|Ahmad' st_firstname, 'Moghise|Majlesi' st_lastname, '20|20' st_score FROM dual)
SELECT t.item_number,
       LEVEL lvl,
       TRIM(regexp_substr(t.st_firstname, '[^|]+', 1, LEVEL)) first_name,
       TRIM(regexp_substr(t.st_lastname, '[^|]+', 1, LEVEL)) last_name,
       TRIM(regexp_substr(t.st_score, '[^|]+', 1, LEVEL)) score
FROM   student_info t
CONNECT BY PRIOR item_number = item_number
           AND PRIOR sys_guid() IS NOT NULL
           AND instr(st_firstname, '|', 1, LEVEL - 1) > 0
ORDER  BY t.item_number, LEVEL;


ITEM_NUMBER        LVL FIRST_NAME      LAST_NAME               SCORE
----------- ---------- --------------- ----------------------- --------
          1          1 Ali             Hashemi                 10
          1          2 Reza            Nosrati                 20
          1          3 Pantea          Yaghobi                 20
          2          1 Maryam          Moghise                 20
          2          2 Ahmad           Majlesi                 20

    注意我已经更新了该connect by子句以将其限制为循环遍历每一行，而不是尝试将所有行分层连接在一起（这种方式很疯狂！如果您的数据集中有很多行，性能也会很差）。请注意，因此，您不再需要distinct, 因为不再生成“重复”行。

    • 1

相关问题