如何关联单列中多值数据与索引表？

要处理多值分隔的Products列与索引表的关联，核心思路是先将多值列拆分为单行记录，再与索引表关联，最后可按需合并结果。以下是主流数据库的具体实现方案：

前提假设 #

• 主表：main_table，包含字段id（主键）、Products（多值字符串，格式如900;190;170）
• 索引表：product_index，包含字段product_id（关联ID）、product_name（产品名称，如900=其他）

SQL Server 实现 #

1. 拆分多值列并关联索引表

SELECT 
    m.id, 
    m.Products AS original_products,
    pi.product_name
FROM main_table m
CROSS APPLY STRING_SPLIT(m.Products, ';') s
LEFT JOIN product_index pi ON s.value = pi.product_id

2. 将关联后的产品名称合并回单行

SELECT 
    m.id, 
    m.Products AS original_products,
    STRING_AGG(pi.product_name, ';') AS product_names
FROM main_table m
CROSS APPLY STRING_SPLIT(m.Products, ';') s
LEFT JOIN product_index pi ON s.value = pi.product_id
GROUP BY m.id, m.Products

MySQL 实现 #

版本8.0.19+（支持STRING_SPLIT）

1. 拆分关联

SELECT 
    m.id, 
    m.Products AS original_products,
    pi.product_name
FROM main_table m
CROSS JOIN STRING_SPLIT(m.Products, ';') s
LEFT JOIN product_index pi ON s.value = pi.product_id

2. 合并名称

SELECT 
    m.id, 
    m.Products AS original_products,
    GROUP_CONCAT(pi.product_name SEPARATOR ';') AS product_names
FROM main_table m
CROSS JOIN STRING_SPLIT(m.Products, ';') s
LEFT JOIN product_index pi ON s.value = pi.product_id
GROUP BY m.id, m.Products

版本8.0.19之前

需借助数字序列拆分字符串：

-- 拆分多值列并关联索引表
SELECT 
    t.id,
    m.Products AS original_products,
    pi.product_name
FROM (
    SELECT 
        m.id,
        SUBSTRING_INDEX(SUBSTRING_INDEX(m.Products, ';', n.n), ';', -1) AS product_id
    FROM main_table m
    CROSS JOIN (
        SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
    ) n
    WHERE n.n <= LENGTH(m.Products) - LENGTH(REPLACE(m.Products, ';', '')) + 1
) t
LEFT JOIN product_index pi ON t.product_id = pi.product_id
LEFT JOIN main_table m ON t.id = m.id

PostgreSQL 实现 #

1. 拆分关联

SELECT 
    m.id, 
    m.Products AS original_products,
    pi.product_name
FROM main_table m
CROSS JOIN unnest(string_to_array(m.Products, ';')) AS s(product_id)
LEFT JOIN product_index pi ON s.product_id = pi.product_id

2. 合并名称

SELECT 
    m.id, 
    m.Products AS original_products,
    STRING_AGG(pi.product_name, ';') AS product_names
FROM main_table m
CROSS JOIN unnest(string_to_array(m.Products, ';')) AS s(product_id)
LEFT JOIN product_index pi ON s.product_id = pi.product_id
GROUP BY m.id, m.Products

额外建议 #

这种将多值存储在单一字段的设计不符合数据库范式，会导致查询效率低下、维护困难。长期来看，建议优化表结构：新建中间关联表（如main_product_rel），存储main_table.id与product_index.product_id的一对一关联记录，从根源上避免多值列的问题。

内容的提问来源于stack exchange，提问作者Ruan du Preez