弹性考勤记录查询SQL中如何处理请假和加班？

SELECT
e.employee_id,
SUM(
CASE
WHEN or.start_time > ws.end_time THEN
— 计算工作时间之外的加班时长
TIMESTAMPDIFF(MINUTE, or.start_time, or.end_time)
WHEN or.end_time < ws.start_time THEN
0
ELSE
–计算加班时间与工作时间的交集外时间
TIMESTAMPDIFF(MINUTE, GREATEST(or.start_time,ws.end_time),or.end_time)
END
) AS overtime_duration
FROM
employees e
LEFT JOIN
overtime_records or ON e.employee_id = or.employee_id
LEFT JOIN
work_schedule ws ON e.employee_id = ws.employee_id AND or.start_time <= ws.work_date AND or.end_time >= ws.work_date
WHERE
or.overtime_type = ‘平日加班’ — 这里可以根据需要筛选不同的加班类型
GROUP BY
e.employee_id;