单元格含多值的两张数据表SQL关联查询实现方案问询

核心思路 #

因为Table1的关联列存储了多个分隔开的值，无法直接和Table2的单值关联列匹配，所以需要先将Table1的多值列拆分为一行对应单个关联值的结构，再执行关联查询。

不同数据库的实现写法 #

1. MySQL 8.0+ / MariaDB #

WITH RECURSIVE split_vals AS (
    -- 递归拆分多值列，假设关联列名为join_col，分隔符为英文逗号
    SELECT 
        t1.*,
        1 AS pos,
        SUBSTRING_INDEX(t1.join_col, ',', 1) AS single_join_val,
        SUBSTRING(join_col, LENGTH(SUBSTRING_INDEX(t1.join_col, ',', 1)) + 2) AS remaining
    FROM Table1 t1
    UNION ALL
    SELECT 
        sv.*,
        pos + 1,
        SUBSTRING_INDEX(sv.remaining, ',', 1),
        SUBSTRING(sv.remaining, LENGTH(SUBSTRING_INDEX(sv.remaining, ',', 1)) + 2)
    FROM split_vals sv
    WHERE sv.remaining <> ''
)
-- 拆分后关联Table2，输出需要的字段
SELECT sv.*, t2.*
FROM split_vals sv
INNER JOIN Table2 t2 ON sv.single_join_val = t2.join_col
-- 按原Table1的主键排序即可得到符合预期的结果
ORDER BY sv.id, sv.pos;

2. PostgreSQL #

-- 用string_to_table直接拆分多值列，unnest转为行
SELECT t1.*, t2.*
FROM Table1 t1,
     unnest(string_to_array(t1.join_col, ',')) AS single_join_val
INNER JOIN Table2 t2 ON single_join_val = t2.join_col
ORDER BY t1.id;

3. Oracle 12c+ #

SELECT t1.*, t2.*
FROM Table1 t1
CROSS APPLY (
    SELECT REGEXP_SUBSTR(t1.join_col, '[^,]+', 1, LEVEL) AS single_join_val
    FROM DUAL
    CONNECT BY REGEXP_SUBSTR(t1.join_col, '[^,]+', 1, LEVEL) IS NOT NULL
) sv
INNER JOIN Table2 t2 ON sv.single_join_val = t2.join_col
ORDER BY t1.id;

注意事项 #

• 上述代码默认多值列的分隔符为英文逗号，如果你实际场景用的是其他分隔符（比如分号、竖线），替换代码中对应的分隔符即可
• 如果你的数据库不支持CTE/递归语法，可以提前对Table1做数据清洗，把多值列拆分为单值的中间表再做关联

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