如何编写SQL查询获取自2021年4月以来上周首次排班的员工列表

原逻辑问题分析 #

• 错误限制了排班总次数：原查询中HAVING count(booking.id) = 1的条件会遗漏上周排班≥2次、且上周前无任何排班记录的符合要求的员工，不符合「首次排班在上周」的统计目标
• 逻辑覆盖不全：原查询用「2021年4月以来总排班数为1+唯一排班在上周」的逻辑替代「首次排班在上周」，无法覆盖上周多次排班的新员工场景

修正后的查询方案 #

核心逻辑：先筛选所有上周有排班的员工，再排除2021年4月至上一周之前存在排班记录的员工，剩余即为目标员工。

WITH last_week AS (
    -- 统一预定义上周的时间范围，避免重复计算
    SELECT 
        date_trunc('week', NOW()) - INTERVAL '1 week' AS week_start,
        date_trunc('week', NOW()) AS week_end
),
-- 提取上周有排班的所有员工ID
last_week_workers AS (
    SELECT DISTINCT worker_id
    FROM booking, last_week
    WHERE start_time >= last_week.week_start
      AND start_time < last_week.week_end
),
-- 提取2021年4月至上一周之前有过排班的员工ID
existed_workers AS (
    SELECT DISTINCT worker_id
    FROM booking, last_week
    WHERE start_time >= '2021-04-01'
      AND start_time < last_week.week_start
)
-- 取差集得到首次排班在上周的员工
SELECT worker_id
FROM last_week_workers
WHERE worker_id NOT IN (SELECT worker_id FROM existed_workers);

补充说明 #

• 如果使用支持EXCEPT语法的数据库（如PostgreSQL、SQL Server等），可以简化差集计算：

WITH last_week AS (
    SELECT 
        date_trunc('week', NOW()) - INTERVAL '1 week' AS week_start,
        date_trunc('week', NOW()) AS week_end
)
SELECT DISTINCT worker_id
FROM booking
WHERE start_time >= (SELECT week_start FROM last_week)
  AND start_time < (SELECT week_end FROM last_week)
EXCEPT
SELECT DISTINCT worker_id
FROM booking
WHERE start_time >= '2021-04-01'
  AND start_time < (SELECT week_start FROM last_week);

• 周起始日适配：date_trunc('week')函数在不同数据库的默认周起始日不同（PostgreSQL默认周一、MySQL默认周日），如果和业务周定义不一致，需要额外调整时间偏移量
• 若需求调整为「员工历史从未有排班，上周首次排班」，只需去掉existed_workers查询中start_time >= '2021-04-01'的条件即可

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