SQL LEFT JOIN多值列拆分关联问题求助

这个问题的核心是你需要先把emp_tbl里包含多个state ID的行拆分成单独的行，再和state_tbl关联——毕竟单个行里的多个ID没法直接和另一张表的单行ID匹配。下面我根据不同的主流数据库给出具体的解决方案：

MySQL 8.0+ 解决方案 #

方法1：递归CTE拆分字符串 #

递归CTE可以逐步把带|分隔的字符串拆分成单行：

WITH split_emp AS (
    SELECT 
        emp,
        -- 去除前后的[]符号，得到纯ID字符串
        TRIM(BOTH '[]' FROM state) AS state_str
    FROM emp_tbl
    UNION ALL
    SELECT 
        emp,
        -- 每次截取|之后的部分，继续拆分
        SUBSTRING(state_str, LOCATE('|', state_str) + 1)
    FROM split_emp
    -- 只有当字符串里还有|时才继续递归
    WHERE LOCATE('|', state_str) > 0
)
SELECT 
    s.emp,
    st.name
FROM split_emp s
-- 每次取|之前的第一个ID做关联
LEFT JOIN state_tbl st ON st.id = SUBSTRING_INDEX(s.state_str, '|', 1)
-- 过滤掉拆分后为空的行
WHERE s.state_str != ''
ORDER BY s.emp, st.name;

方法2：利用JSON_TABLE（更简洁） #

因为你的state格式类似JSON数组（只是用|代替了逗号），可以先转成合法JSON数组再拆分：

SELECT 
    e.emp,
    st.name
FROM emp_tbl e
JOIN JSON_TABLE(
    -- 把|替换成逗号，转成合法JSON数组
    REPLACE(e.state, '|', ','),
    '$[*]' COLUMNS (state_id INT PATH '$')
) AS jt
LEFT JOIN state_tbl st ON st.id = jt.state_id
ORDER BY e.emp, st.name;

PostgreSQL 解决方案 #

用string_to_array把字符串转成数组，再用unnest展开成多行：

SELECT 
    e.emp,
    st.name
FROM emp_tbl e
-- 拆分字符串并展开为多行
CROSS JOIN UNNEST(
    string_to_array(TRIM(BOTH '[]' FROM e.state), '|')
) AS split_state(state_id)
-- 把拆分后的字符串转成整数再关联
LEFT JOIN state_tbl st ON st.id = split_state.state_id::INT
ORDER BY e.emp, st.name;

SQL Server 2016+ 解决方案 #

用内置的STRING_SPLIT函数直接拆分字符串：

SELECT 
    e.emp,
    st.name
FROM emp_tbl e
-- 拆分带|分隔的字符串为多行
CROSS APPLY STRING_SPLIT(TRIM(BOTH '[]' FROM e.state), '|') AS split_state
LEFT JOIN state_tbl st ON st.id = split_state.value
ORDER BY e.emp, st.name;

补充说明 #

• 如果你确定emp_tbl里的state ID都存在于state_tbl中，把LEFT JOIN换成INNER JOIN也可以，结果是一样的。
• 拆分后的ID如果是字符串类型，需要根据数据库情况转成整数（比如PostgreSQL里的::INT），确保和state_tbl.id的类型匹配。

内容的提问来源于stack exchange，提问作者br.nz