如何基于ID关联TableA与TableB，匹配分组并查询对应slug？

问题核心 #

原查询使用FIND_IN_SET(a.description, b.value)失败，因为tableA.description包含非ID文本（如"Hello I am 1"），无法直接与tableB.value中的纯ID条目匹配。需要先从description中提取数字ID，再拆分value的多值条目，最后进行匹配。

适用MySQL 8.0+的SQL语句（推荐） #

利用CTE和STRING_SPLIT函数拆分字符串，结合正则表达式提取ID：

WITH split_b AS (
    -- 拆分tableB的多值value为单个分组ID
    SELECT 
        b.id AS b_id,
        TRIM(s.value) AS group_id
    FROM tableB b
    JOIN STRING_SPLIT(b.`value`, ',') s
),
split_a AS (
    -- 从tableA.description中提取数字ID并拆分
    SELECT 
        a.id AS a_id,
        a.slug,
        TRIM(s.id_str) AS group_id
    FROM tableA a
    -- 先清除描述中的非数字/逗号字符，得到纯ID列表
    JOIN STRING_SPLIT(REGEXP_REPLACE(a.description, '[^0-9,]', ''), ',') s
    WHERE TRIM(s.id_str) != '' -- 过滤空值
)
-- 关联拆分后的结果，输出匹配记录
SELECT 
    ROW_NUMBER() OVER() AS id, -- 生成自增ID对应预期结果
    sb.group_id AS value,
    sa.slug
FROM split_b sb
JOIN split_a sa ON sb.group_id = sa.group_id
ORDER BY id;

适用MySQL 5.x的SQL语句 #

由于MySQL 5.x不支持STRING_SPLIT和CTE，需用数字辅助表拆分字符串：

1. 先创建数字辅助表（按需调整数值范围）：

CREATE TABLE numbers (n INT);
INSERT INTO numbers VALUES (1),(2),(3),(4),(5);

2. 执行匹配查询：

SELECT 
    ROW_NUMBER() OVER() AS id,
    sb.group_id AS value,
    sa.slug
FROM (
    -- 拆分tableB的value
    SELECT 
        b.id AS b_id,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(b.`value`, ',', n.n), ',', -1)) AS group_id
    FROM tableB b
    JOIN numbers n ON n.n <= LENGTH(b.`value`) - LENGTH(REPLACE(b.`value`, ',', '')) + 1
) sb
JOIN (
    -- 提取tableA.description中的ID并拆分
    SELECT 
        a.id AS a_id,
        a.slug,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_REPLACE(a.description, '[^0-9,]', ''), ',', n.n), ',', -1)) AS group_id
    FROM tableA a
    JOIN numbers n ON n.n <= LENGTH(REGEXP_REPLACE(a.description, '[^0-9,]', '')) - LENGTH(REPLACE(REGEXP_REPLACE(a.description, '[^0-9,]', ''), ',', '')) + 1
    WHERE TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(REGEXP_REPLACE(a.description, '[^0-9,]', ''), ',', n.n), ',', -1)) != ''
) sa ON sb.group_id = sa.group_id
ORDER BY id;

效果验证 #

针对你的示例数据，上述查询会输出与预期一致的结果：

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