Oracle 12：加入逗号分隔的列表吗？

Question

假设我有一个名为“ companies”的视图，该视图无法修改：

+------------+--------------------+--------+--------+------------------------+
| company_id | company_name       | ceo    | cfo    | legal_contacts         |
+------------+--------------------+--------+--------+------------------------+
| 1          | johnson and son    | pid111 | pid333 | pid444, pid567, pid999 |
| 2          | Pepperville apples | pid777 |        | pid345                 |
| 3          | Cats LTD           | pid123 | pid321 |                        |
+------------+--------------------+--------+--------+------------------------+

还有一个称为“联系人”的视图：

+-----------+-------+----------------------+
| person_id | name  | email                |
+-----------+-------+----------------------+
| pid111    | john  | [e-mail]       |
| pid333    | steve | [e-mail]  |
| pid444    | mary  | [e-mail]     |
| pid999    | joe   | [e-mail] |
| pid777    | louis |                      |
| pid345    | carol | [e-mail] |
| pid321    | ellen | [e-mail]  |
+-----------+-------+----------------------+

最终目标是让我编写一个查询，该查询可以交叉引用人员ID并显示电子邮件和公司，例如：

+---------+----------------+-----------------------------------------+
| company | ceo            | legal_contacts                          |
+---------+----------------+-----------------------------------------+
| 1       | [e-mail] | [e-mail], [e-mail] |
| 2       |                | [e-mail]                     |
| 3       |                |                                         |
+---------+----------------+-----------------------------------------+

我有没有办法在不编写函数或过程的情况下在查询中加入或处理此逗号分隔的标识符列表？

您可以假设“合法联系人”最多具有25个标识符，且始终采用相同格式，且始终用逗号分隔

Answer 1

您可以拆分companies.legal_contacts使用正则表达式的列表，然后将结果集与联系人联系以获取电子邮件地址（也可以两次联系以获取ceo邮件），然后使用listagg函数重新连接电子邮件：

SELECT co.company_id, p1.email, LISTAGG(p2.email, ', ') WITHIN GROUP (ORDER BY p2.email)
  FROM (
        SELECT DISTINCT company_id, ceo, REGEXP_SUBSTR(legal_contacts, '[^, ]+', 1, LEVEL) AS single_contact   
          FROM COMPANIES
       CONNECT BY REGEXP_SUBSTR(legal_contacts, '[^, ]+', 1, LEVEL) IS NOT NULL) co
  LEFT JOIN CONTACTS p1 ON co.ceo = p1.person_id
  LEFT JOIN CONTACTS p2 ON co.single_contact = p2.person_id
 GROUP BY co.company_id, p1.email;

如果companies.legal_contacts可以包含许多值，则由于性能原因，正则表达式的使用会有所变化，因此您必须使用MULTISET。