基于日期范围获取员工排班模板的MariaDB查询优化问题

针对你遇到的三个问题，以下是优化后的查询语句及问题解决说明：

优化SQL查询 #

WITH date_range AS (
    -- 生成查询日期范围内的所有日期及对应的工作日（1=周一, 4=周四, 6=周六）
    SELECT 
        date_add('2023-09-25', INTERVAL seq DAY) AS schedule_date,
        WEEKDAY(date_add('2023-09-25', INTERVAL seq DAY)) + 1 AS work_day
    FROM seq_0_to_6  -- 对应7天的日期范围，可根据实际查询天数调整
),
valid_schedules AS (
    -- 获取员工ID5在查询日期范围内有效的最新排班模板（排除已废弃的记录）
    SELECT 
        st.employee_id,
        st.work_day,
        st.shift_start,
        st.shift_end,
        -- 取每个员工+工作日的最新生效模板（按生效开始日期倒序）
        ROW_NUMBER() OVER (PARTITION BY st.employee_id, st.work_day ORDER BY st.effective_start_date DESC) AS rn
    FROM employee_schedule_templates st
    WHERE st.employee_id = 5
      -- 生效时间覆盖查询日期范围，且未废弃（effective_end_date为NULL或晚于查询结束日期）
      AND st.effective_start_date <= '2023-10-01'
      AND (st.effective_end_date IS NULL OR st.effective_end_date >= '2023-09-25')
)
SELECT 
    dr.schedule_date,
    vs.work_day,
    vs.shift_start,
    vs.shift_end,
    -- 正确计算跨零点班次的工时：如果结束时间早于开始时间，加24小时再计算差值
    TIMESTAMPDIFF(HOUR, vs.shift_start, 
        IF(vs.shift_end < vs.shift_start, ADDTIME(vs.shift_end, '24:00:00'), vs.shift_end)
    ) AS work_hours
FROM date_range dr
LEFT JOIN valid_schedules vs 
    ON dr.work_day = vs.work_day 
    AND vs.rn = 1  -- 只保留每个工作日的最新有效模板
ORDER BY dr.schedule_date;

问题解决说明 #

1. 修复周四排班未显示问题 #

通过date_rangeCTE生成查询范围内的所有日期及对应工作日，再与有效排班模板关联，确保每个工作日的排班都能被匹配到，避免原查询中日期与工作日映射不完整或关联条件缺失的问题。

2. 移除已废弃的周六旧记录 #

• 在valid_schedulesCTE中，通过effective_end_date筛选掉已废弃的记录（仅保留未废弃或生效时间覆盖查询范围的模板）。
• 使用ROW_NUMBER()窗口函数按employee_id和work_day分组，取每个工作日的最新生效模板，确保旧的废弃记录不会被选中。

3. 修正跨零点班次工时负数问题 #

使用IF(vs.shift_end < vs.shift_start, ADDTIME(vs.shift_end, '24:00:00'), vs.shift_end)判断跨零点情况，将结束时间加24小时后再用TIMESTAMPDIFF计算工时，确保结果为正数。

额外说明 #

如果你的排班表结构与假设不同（比如工作日定义、生效字段名不同），请根据实际表结构调整字段名和逻辑；若查询范围天数不固定，可将seq_0_to_6替换为动态生成日期范围的递归CTE。

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