SQL查询未提交问卷员工结果异常问题排查求助

问题核心 #

主员工列表共340人，单独查询已提交问卷的员工为220人，预期未提交人数应为120，但通过NOT EXISTS关联后的查询结果显示未提交人数少20（仅100人），说明存在20名员工被错误判定为已提交，或主/子查询的关联逻辑存在漏洞。

1. 校验邮箱匹配逻辑的一致性 #

必须确保主查询和子查询中，用于匹配的员工邮箱生成逻辑完全统一：

• 主查询中用COALESCE(workemail, homeemail)生成empEmail，子查询中也要用同样的规则处理提交表中的邮箱（如果提交表同时存储了work和home邮箱）；
• 若提交表仅存储员工提交时使用的单个邮箱，则需直接用该字段与主查询的COALESCE(workemail, homeemail)做匹配，不能在子查询中随意替换为其他邮箱字段。

2. 排查邮箱字段的格式差异 #

邮箱格式不一致是最常见的匹配错误原因：

• 大小写问题：部分数据库默认大小写敏感（如PostgreSQL），user@company.com和User@Company.com会被判定为不同值，需统一转成小写/大写后匹配（如LOWER(COALESCE(m.workemail, m.homeemail)) = LOWER(s.email)）；
• 空格/不可见字符：邮箱前后可能存在空格、换行符等，需用TRIM()函数清理后再匹配；
• 格式错误：检查是否存在少@符号、域名拼写错误的邮箱，这类数据会导致关联失败或错误匹配。

3. 验证NOT EXISTS的关联条件完整性 #

• 时间范围缺失：需求是每周统计未提交，若子查询未限定本周的提交日期（未关联DateDetails的时间条件），会把历史提交的员工也纳入已提交范围，导致未提交人数减少；
• 表关联错误：确认SubmittedList与DateDetails的关联键是否正确（如员工ID、提交记录ID），错误关联可能产生冗余提交记录，导致更多员工被误判为已提交。

4. 检查主表的特殊数据情况 #

• 重复邮箱：主表中若存在多个员工共享同一个邮箱（如两人的homeemail相同且workemail为空），只要其中一人提交问卷，另一人会被误判为已提交；
• 空邮箱例外：虽然需求说明workemail和homeemail不同时为空，但需实际验证主表是否存在双空记录，这类记录的empEmail为空，无法匹配任何提交记录（不过此情况会导致未提交人数变多，与当前问题不符，但仍需排查）。

5. 反向查询定位异常数据 #

执行以下SQL，找出被错误判定为已提交的员工，对比提交表数据即可定位原因：

-- 找出被判定为已提交的员工邮箱及主表信息
SELECT 
    COALESCE(m.workemail, m.homeemail) AS empEmail,
    m.*
FROM MasterList m
WHERE EXISTS (
    SELECT 1
    FROM SubmittedList s
    JOIN DateDetails d ON s.submission_id = d.submission_id -- 替换为实际关联键
    WHERE 
        -- 保持和原查询一致的邮箱匹配逻辑
        LOWER(COALESCE(m.workemail, m.homeemail)) = LOWER(s.submitter_email)
        -- 加上本周时间范围（替换为实际的时间参数）
        AND d.submit_date >= DATE_TRUNC('week', CURRENT_DATE)
        AND d.submit_date < DATE_TRUNC('week', CURRENT_DATE) + INTERVAL '7 days'
)

将上述结果与单独查询的220条已提交邮箱对比，不在其中的邮箱即为导致异常的根源。

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