如何关联汇总表与数据表以保留行级数据

核心思路 #

要实现Table A的job匹配到Table B中包含该job的角色组，关键是判断Table A的job是否存在于Table B的jobs组字符串中，同时关联相同的office（默认同一office下的job组匹配）。之前like失败大概率是没处理好分隔符边界（比如避免"Dev"误匹配"Developer"这类部分字符串），split方法失败可能是未正确展开组内job再做匹配。

具体实现（分数据库示例） #

1. MySQL 方案 #

用FIND_IN_SET函数精准匹配逗号分隔的元素，规避部分匹配问题：

SELECT 
    a.office,
    b.job_group AS job,
    b.count
FROM 
    Table_A a
JOIN 
    Table_B b ON a.office = b.office 
    AND FIND_IN_SET(a.job, REPLACE(b.jobs, ' ', '')) > 0;

说明：如果Table B的jobs组带空格（比如"Developer, Engineer"），用REPLACE去掉空格，保证FIND_IN_SET能正确识别元素。

2. PostgreSQL 方案 #

用STRING_TO_ARRAY把jobs组分拆成数组，再通过ANY判断匹配：

SELECT 
    a.office,
    b.job_group AS job,
    b.count
FROM 
    Table_A a
JOIN 
    Table_B b ON a.office = b.office 
    AND a.job = ANY(STRING_TO_ARRAY(b.jobs, ','));

若jobs组含空格，可追加TRIM处理：

AND a.job = ANY(SELECT TRIM(UNNEST(STRING_TO_ARRAY(b.jobs, ','))));

3. SQL Server 方案 #

用STRING_SPLIT展开jobs组后再关联匹配：

SELECT 
    a.office,
    b.job_group AS job,
    b.count
FROM 
    Table_A a
JOIN 
    Table_B b ON a.office = b.office
JOIN 
    STRING_SPLIT(b.jobs, ',') s ON TRIM(s.value) = a.job;

特殊情况处理 #

如果一个job属于多个角色组，会返回多条记录，可根据业务需求用ROW_NUMBER()筛选（比如取Count最大的组）：

-- MySQL示例：取每个员工对应Count最大的组
SELECT office, job, count
FROM (
    SELECT 
        a.office,
        b.job_group AS job,
        b.count,
        ROW_NUMBER() OVER (PARTITION BY a.office, a.job ORDER BY b.count DESC) rn
    FROM 
        Table_A a
    JOIN 
        Table_B b ON a.office = b.office 
        AND FIND_IN_SET(a.job, REPLACE(b.jobs, ' ', '')) > 0
) t
WHERE rn = 1;

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