SQL如何查询指定月份无薪资记录的所有员工

前提说明 #

• 需提前维护全量员工基础表（假设表名employee_info，主键为employee_id，存储所有需要纳入统计的员工ID），仅靠薪资表无法完成统计：薪资表仅保留有发薪记录的员工数据，无未发薪员工的标识信息。
• 目标查询月份统一用参数@target_month表示，格式为'YYYY-MM-01'，比如示例中的'2021-10-01'，适配不同查询需求时直接替换参数即可。

最优查询方案（适配大规模数据集） #

采用LEFT JOIN+空值判断的方案，性能远优于NOT IN写法，尤其百万级以上数据集时优势明显，同时可避免NOT IN遇到空值的逻辑错误：

SELECT e.employee_id
FROM employee_info e
LEFT JOIN salary s 
  ON e.employee_id = s.`Employee ID`
  AND s.`Month of Salary` = @target_month
WHERE s.`Employee ID` IS NULL;

方案解释 #

• 先关联全量员工表和薪资表，关联条件同时限定薪资所属月份，关联后没有对应月份薪资记录的员工，薪资表的所有字段都会返回NULL
• 最后通过WHERE条件筛选薪资表ID为NULL的记录，即为目标月份没有发薪记录的员工
• 如果需要同时返回员工姓名、部门等其他信息，直接在SELECT后追加employee_info表的对应字段即可

场景验证（对应给出的示例） #

当@target_month = '2021-10-01'时：

• 员工3在薪资表有对应月份的记录，关联后薪资表ID不为空，会被过滤
• 员工1、2没有对应月份的薪资记录，关联后薪资表ID为NULL，会被查询出来，符合预期结果

可选方案（无单独员工表时使用） #

如果没有维护单独的员工表，只能从薪资表的历史记录里提取所有出现过的员工ID作为全量统计范围，可以用以下写法：

SELECT all_emp.employee_id
FROM (
  SELECT DISTINCT `Employee ID` AS employee_id FROM salary
) all_emp
LEFT JOIN salary s 
  ON all_emp.employee_id = s.`Employee ID`
  AND s.`Month of Salary` = @target_month
WHERE s.`Employee ID` IS NULL;

注意：该方案统计范围仅包含历史有过发薪记录的员工，如果有新入职还没发过薪的员工会被遗漏，仅建议临时查询使用

样例数据示意图 #

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