Oracle 12：无需自定义函数，如何关联逗号分隔ID列表？

当然可以实现！虽然处理逗号分隔的ID列表确实有点棘手，但利用SQL的内置字符串函数和公共表表达式（CTE），完全能在纯查询里完成这个需求，不用写任何自定义函数或存储过程。

下面针对你的需求给出具体的查询方案，我会分两种常见数据库（PostgreSQL和MySQL 8+）来演示，核心思路是一致的：先拆分逗号分隔的ID为单独行，关联联系人表获取邮箱，再重新拼接成逗号分隔的字符串。

PostgreSQL 实现 #

WITH split_legal_contacts AS (
    SELECT 
        c.company_id,
        c.company_name,
        c.ceo,
        -- 拆分逗号分隔的legal_contacts为单个ID，同时去掉前后空格
        trim(unnest(string_to_array(c.legal_contacts, ','))) AS legal_pid
    FROM companies c
),
legal_emails AS (
    SELECT 
        slc.company_id,
        -- 聚合同一个公司的合法联系人邮箱，空ID对应空字符串
        string_agg(coalesce(ct.email, ''), ', ') AS legal_contacts_email
    FROM split_legal_contacts slc
    LEFT JOIN contacts ct ON slc.legal_pid = ct.person_id
    -- 过滤空的ID条目
    WHERE slc.legal_pid IS NOT NULL AND slc.legal_pid != ''
    GROUP BY slc.company_id
),
ceo_emails AS (
    SELECT 
        c.company_id,
        -- 处理CEO邮箱为空的情况
        coalesce(ct.email, '') AS ceo_email
    FROM companies c
    LEFT JOIN contacts ct ON c.ceo = ct.person_id
)
SELECT 
    ce.company_id AS company,
    ce.ceo_email AS ceo,
    -- 确保没有合法联系人时显示空字符串而非NULL
    coalesce(le.legal_contacts_email, '') AS legal_contacts
FROM ceo_emails ce
LEFT JOIN legal_emails le ON ce.company_id = le.company_id
ORDER BY ce.company_id;

MySQL 8+ 实现 #

WITH RECURSIVE split_legal_contacts AS (
    -- 初始化：获取第一个ID
    SELECT 
        company_id,
        company_name,
        ceo,
        legal_contacts,
        1 AS pos,
        SUBSTRING_INDEX(SUBSTRING_INDEX(legal_contacts, ',', 1), ',', -1) AS legal_pid
    FROM companies
    WHERE legal_contacts IS NOT NULL AND legal_contacts != ''
    UNION ALL
    -- 递归：依次获取后续的ID
    SELECT 
        company_id,
        company_name,
        ceo,
        legal_contacts,
        pos + 1,
        SUBSTRING_INDEX(SUBSTRING_INDEX(legal_contacts, ',', pos + 1), ',', -1) AS legal_pid
    FROM split_legal_contacts
    -- 递归终止条件：直到所有ID都被拆分
    WHERE pos < LENGTH(legal_contacts) - LENGTH(REPLACE(legal_contacts, ',', '')) + 1
),
legal_emails AS (
    SELECT 
        company_id,
        -- 聚合邮箱，空ID对应空字符串
        GROUP_CONCAT(COALESCE(ct.email, '') SEPARATOR ', ') AS legal_contacts_email
    FROM split_legal_contacts slc
    LEFT JOIN contacts ct ON TRIM(slc.legal_pid) = ct.person_id
    GROUP BY company_id
),
ceo_emails AS (
    SELECT 
        company_id,
        COALESCE(ct.email, '') AS ceo_email
    FROM companies c
    LEFT JOIN contacts ct ON c.ceo = ct.person_id
)
SELECT 
    ce.company_id AS company,
    ce.ceo_email AS ceo,
    COALESCE(le.legal_contacts_email, '') AS legal_contacts
FROM ceo_emails ce
LEFT JOIN legal_emails le ON ce.company_id = le.company_id
ORDER BY ce.company_id;

核心思路拆解 #

1. 拆分ID列表：用递归CTE（MySQL）或数组拆分函数（PostgreSQL）把逗号分隔的legal_contacts拆成单独的行，同时用trim/TRIM清理ID前后的空格。
2. 关联联系人邮箱：把拆分后的每个ID和contacts表关联，获取对应的邮箱，用coalesce/COALESCE处理没有匹配到邮箱的情况（比如原数据里的pid567不存在于联系人表中）。
3. 聚合邮箱列表：用字符串聚合函数（string_agg/GROUP_CONCAT）把同一个公司的合法联系人邮箱重新拼接成逗号分隔的字符串。
4. 处理CEO邮箱：单独关联CEO的ID和联系人表，同样处理空值情况。
5. 合并结果：把CEO邮箱和合法联系人邮箱的结果关联，得到最终的目标格式。

这种方法完全依赖SQL内置功能，不需要自定义函数或存储过程，而且因为你提到legal_contacts最多只有25个ID，递归的次数非常有限，性能不会有问题。

内容的提问来源于stack exchange，提问作者Paul