如何用SQL根据Person表过滤Schedule表中不存在的员工值？

问题描述 #

• 表Person：包含ID、Name字段，现有员工记录：Jane、Joe、Jose
• 表Schedule：包含Date、Employees字段，Employees为逗号分隔的员工名称字符串
• 需求：剔除Schedule.Employees中不存在于Person表的员工名，无有效员工则该字段为空，且禁止修改现有表结构

可行性结论 #

可以实现，核心思路是先拆分逗号分隔字符串为单个员工名，过滤出存在于Person表的有效名称，再重新拼接为逗号分隔字符串。以下是主流数据库的具体实现方案：

MySQL 8.0+ 版本 #

SELECT 
    s.Date,
    GROUP_CONCAT(p.Name SEPARATOR ',') AS Employees
FROM Schedule s
LEFT JOIN (
    -- 拆分Schedule的Employees字段为单行记录
    SELECT 
        s.Date,
        TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(s.Employees, ',', n.n), ',', -1)) AS emp_name
    FROM Schedule s
    -- 生成数字序列，覆盖最大员工数量，可按需扩展
    CROSS JOIN (
        SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
        UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
    ) n
    WHERE s.Employees IS NOT NULL AND s.Employees != ''
    HAVING emp_name != ''
) split_emp ON s.Date = split_emp.Date
LEFT JOIN Person p ON split_emp.emp_name = p.Name
GROUP BY s.Date
HAVING GROUP_CONCAT(p.Name SEPARATOR ',') IS NOT NULL

UNION ALL

-- 补充无有效员工的记录，返回空字符串
SELECT Date, '' AS Employees
FROM Schedule
WHERE Date NOT IN (
    SELECT s.Date
    FROM Schedule s
    LEFT JOIN (
        SELECT 
            s.Date,
            TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(s.Employees, ',', n.n), ',', -1)) AS emp_name
        FROM Schedule s
        CROSS JOIN (
            SELECT 1 n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
            UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
        ) n
        WHERE s.Employees IS NOT NULL AND s.Employees != ''
        HAVING emp_name != ''
    ) split_emp ON s.Date = split_emp.Date
    LEFT JOIN Person p ON split_emp.emp_name = p.Name
    GROUP BY s.Date
    HAVING GROUP_CONCAT(p.Name SEPARATOR ',') IS NOT NULL
);

PostgreSQL 版本 #

SELECT 
    s.Date,
    COALESCE(STRING_AGG(p.Name, ','), '') AS Employees
FROM Schedule s
LEFT JOIN LATERAL (
    -- 拆分逗号分隔字符串为单行
    SELECT TRIM(unnest(string_to_array(s.Employees, ','))) AS emp_name
    WHERE s.Employees IS NOT NULL AND s.Employees != ''
) split_emp ON true
LEFT JOIN Person p ON split_emp.emp_name = p.Name
GROUP BY s.Date;

SQL Server 版本 #

SELECT 
    s.Date,
    COALESCE(STRING_AGG(p.Name, ','), '') AS Employees
FROM Schedule s
LEFT JOIN (
    -- 拆分逗号分隔字符串
    SELECT 
        s.Date,
        TRIM(value) AS emp_name
    FROM Schedule s
    CROSS APPLY STRING_SPLIT(s.Employees, ',')
    WHERE s.Employees IS NOT NULL AND s.Employees != ''
) split_emp ON s.Date = split_emp.Date
LEFT JOIN Person p ON split_emp.emp_name = p.Name
GROUP BY s.Date;

关键逻辑说明 #

• 字符串拆分：利用数据库原生函数（如SQL Server的STRING_SPLIT、PostgreSQL的string_to_array+unnest，MySQL用SUBSTRING_INDEX结合数字序列）将逗号分隔的字段拆分为单个员工名的行记录
• 有效员工过滤：通过左连接Person表，筛选出存在的员工名称
• 结果拼接：用聚合函数（GROUP_CONCAT/STRING_AGG）将有效员工名重新拼接为逗号分隔字符串，无有效员工时通过COALESCE或补全查询返回空字符串

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