string - Oracle 查询 - 用逗号分隔的数据连接

Question

表名称:crm_mrdetails

 id | mr_name | me_email     | mr_mobile  | mr_doctor|
 -----------------------------------------------------
 1  | John    |abc@gmail.com | 1234555555 | ,1,2,3   |

表名:crm_mr_doctor

id | dr_name     | specialization|  
----------------------------------  
1  | Abhishek    | cordiologist  |
2  | Krishnan    | Physician     |
3  | Krishnan    | Nurse         |

mrdetails.mr_doctor 中的串联值是 mr_doctor.id 的外键。我需要加入他们来产生这样的输出:

 id | mr_name | me_email     |Doctor_Specialization|
 -------------------------------------------------
 1  | John    |abc@gmail.com |cordiologist,Physician,Nurse|

我是 Oracle 的新手，我使用的是 Oracle 12C。非常感谢任何帮助。

Answer 1

首先我们必须承认这是一个糟糕的数据模型。 mr_doctor 列违反了 First Normal Form .这不是一些深奥的理论点。不在 1NF 中意味着我们必须编写更多代码来查找键的含义，而不是使用标准的 SQL 连接语法。这也意味着我们不能依赖包含有效 ID 的列:mr_doctor 可以包含任何旧的废话，我们必须编写一个可以处理它的查询。参见 Is storing a delimited list in a database column really that bad?有关更多信息。

无论如何。这是一个解决方案，它使用正则表达式将 mr_doctor 列拆分为 ID，然后将它们连接到 mr_doctor 表。 specialization 列被连接起来以产生所需的输出。

select mrdet.id, 
       mrdet.mr_name,
       mrdet.me_email,
       listagg(mrdoc.specialization, ',') 
                     within group (order by mrdoc.specialization) as doctor_specialization
from mr_details mrdet
     join (
        select distinct id, 
               regexp_substr(mr_doctor, '(,?)([0-9]+)(,?)', 1, level, null, 2) as dr_id
        from mr_details 
        connect by level <= regexp_count(mr_doctor, '(,?)([0-9]+)')
       ) as mrids
    on mrids.id = mrdet.id
    left outer join mr_doctor mrdoc
       on mrids.dr_id = mr_doc.id
group by mrdet.id, 
       mrdet.mr_name,
       mrdet.me_email
/

尽管数据模型很脆弱，但此解决方案具有相当的弹性。如果字符串中有太多逗号或空格，它将返回结果。它会忽略字母或其他非数字的值。如果提取的数字与 mr_doctor 表中的 ID 不匹配，它不会抛出。显然，出于这些原因，结果是不可信的，但这是劣质数据模型的部分代价。

Can you please explain the following: (,?)([0-9]+)(,?)

该模式匹配零个或一个逗号 后跟一个或多个数字 后跟零个或一个逗号。也许匹配模式中的 (,?) 并不是绝对必要的。但是，如果没有它们，此字符串 2 3 4 将匹配与此字符串 2,3,4 相同的三个 ID。也许那是对的也许不是。当外键存储在 CSV 列中而不是通过适当的约束强制执行时，“正确”甚至意味着什么？