SQL如何水平展示单列关联值 解决多表关联重复行问题

问题说明 #

现有两张业务表，需要基于关联关系聚合生成RD维度的负责人映射表，原有多表LEFT JOIN方案存在表关联冗余、结果重复行多的问题。

测试表T1结构 #

| Key  | Title | Developer | Links               |
| ---- | ----- | --------- | ------------------- |
| RD-1 | Gray  | Jon Cruz  | PD-2, LD-4          |
| RD-2 | Blue  | Drew Lee  | PD-30, LD-12, PD-23 |

测试表T2结构 #

| Key   | Assignee   | Links       |
| ----- | ---------- | ----------- |
| PD-30 | Kurt Penn  | RD-2        |
| PD-2  | Jury Souk  | RD-1, LD-4  |
| LD-4  | Grace Chen | RD-1, PD-2  |
| LD-12 | Gram Bron  | RD-2, PD-23 |
| PD-23 | Peter Tiu  | RD-2, LD-12 |

期望输出 #

| Key  | Title | Developer | PD Assignee          | LD Assignee |
| ---- | ----- | --------- | -------------------- | ----------- |
| RD-1 | Gray  | Jon Cruz  | Jury Souk            | Grace Chen  |
| RD-2 | Blue  | Drew Lee  | Kurt Penn, Peter Tiu | Gram Bron   |

原有实现问题 #

原方案拆分多张独立表做LEFT JOIN，冗余度高，且未做分组聚合会产生大量重复行，原参考代码如下：

SELECT 
    T1.Key, T1.Title, T1.Developer, T2.'Assignee' PD Assignee, 
    T3.'Assignee' 'PD2 Assignee', T4.'Assignee' 'LD Assignee'
FROM 
    T1 LEFT JOIN T2 ON T1.'Key' IN T2.'Links'
    LEFT JOIN T3 ON T1.'Key' IN T3.'Links'
    LEFT JOIN T4 ON T1.'Key' IN T4.'Links';

优化实现方案 #

核心逻辑无需拆分多张物理表，仅需一次关联+条件聚合即可完成需求，注意关联时要处理逗号分隔字段的匹配逻辑，避免误匹配、漏匹配，根据常用数据库类型给出对应可执行代码：

MySQL 版本 #

使用FIND_IN_SET匹配逗号分隔的关联值，GROUP_CONCAT按前缀分类拼接负责人：

SELECT
    t1.`Key`,
    t1.Title,
    t1.Developer,
    GROUP_CONCAT(CASE WHEN t2.`Key` LIKE 'PD-%' THEN t2.Assignee END ORDER BY t2.`Key` SEPARATOR ', ') AS `PD Assignee`,
    GROUP_CONCAT(CASE WHEN t2.`Key` LIKE 'LD-%' THEN t2.Assignee END ORDER BY t2.`Key` SEPARATOR ', ') AS `LD Assignee`
FROM T1 t1
LEFT JOIN T2 t2
  -- 先替换Links字段里的空格，避免逗号后带空格导致匹配失败
  ON FIND_IN_SET(t1.`Key`, REPLACE(t2.Links, ' ', '')) > 0
GROUP BY t1.`Key`, t1.Title, t1.Developer;

PostgreSQL 版本 #

使用数组拆分做关联匹配，STRING_AGG按前缀分类拼接负责人：

SELECT
    t1."Key",
    t1.Title,
    t1.Developer,
    STRING_AGG(CASE WHEN t2."Key" LIKE 'PD-%' THEN t2.Assignee END, ', ' ORDER BY t2."Key") AS "PD Assignee",
    STRING_AGG(CASE WHEN t2."Key" LIKE 'LD-%' THEN t2.Assignee END, ', ' ORDER BY t2."Key") AS "LD Assignee"
FROM T1 t1
LEFT JOIN T2 t2
  ON EXISTS (
    SELECT 1 
    FROM UNNEST(string_to_array(REPLACE(t2.Links, ' ', ''), ',')) lk
    WHERE lk = t1."Key"
  )
GROUP BY t1."Key", t1.Title, t1.Developer;

注意事项 #

• 逗号分隔字段属于反范式设计，长期使用建议拆成关联关系表存储，避免字符串匹配的性能问题和匹配错误
• 关联匹配前必须去除逗号后的空格，否则会出现值匹配遗漏
• 前缀判断PD、LD类型时要写全'PD-%'、'LD-%'的匹配规则，避免字段值前缀相似导致分类错误

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