Oracle 12:加入以逗号分隔的列表?

问题描述：

假设我有一个无法修改的名为公司"的视图:

Say I have a view called 'companies' which I cannot modify:

+------------+--------------------+--------+--------+------------------------+
| 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 |                        |
+------------+--------------------+--------+--------+------------------------+

还有一个名为联系人"的视图:

and also a view called 'contacts':

+-----------+-------+----------------------+
| person_id | name  | email                |
+-----------+-------+----------------------+
| pid111    | john  | john@gmail.com       |
| pid333    | steve | funkylover@mail.com  |
| pid444    | mary  | mar123@ymail.com     |
| pid999    | joe   | joe.bloggs@gmail.com |
| pid777    | louis |                      |
| pid345    | carol | carol@carolssite.com |
| pid321    | ellen | ellen.deg@gmail.com  |
+-----------+-------+----------------------+

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

And the end goal is for me to write a query which cross-references the person ids and shows emails and company like:

+---------+----------------+-----------------------------------------+
| company | ceo            | legal_contacts                          |
+---------+----------------+-----------------------------------------+
| 1       | john@gmail.com | mary123@ymail.com, joe.bloggs@gmail.com |
| 2       |                | carol@carossite.com                     |
| 3       |                |                                         |
+---------+----------------+-----------------------------------------+

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

Is there a way for me to join or process this comma separated list of identifiers in a query without writing a function or process?

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

You can assume 'legal contacts' will have at most 25 identifiers, always in the same format, always separated by a comma

答

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

you can split the list of companies.legal_contacts using regular expression, then join the result set with contacts to get the email addresses (join twice to get ceo mail too) and then re-concatenate the emails using listagg function:

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.

if the companies.legal_contacts can contain many values, the use of regular expression changes a bit for performance reasons and you must use a MULTISET.

Oracle 12:加入以逗号分隔的列表?

相关推荐