Mysql 获取连续11天未考勤的员工

发布于08月17日

我正在试着从数据库中找到连续11天没有标记考勤的员工为此，我有员工表和考勤表，但我面临的问题是我在考勤表中没有记录，所以我如何才能获得它

我try 了很多查询，其中一些是

SELECT e.name, e.full_name
FROM tabEmployee e
LEFT JOIN (
    SELECT 
        employee,
        MIN(attendance_date) AS first_attendance_date
    FROM tabAttendance
    GROUP BY employee
) ar ON e.name = ar.employee
WHERE ar.first_attendance_date IS NULL OR 
      NOT EXISTS (
          SELECT 1
          FROM tabAttendance
          WHERE employee = e.name
          AND attendance_date >= ar.first_attendance_date
          AND attendance_date < DATE_ADD(ar.first_attendance_date, INTERVAL 11 DAY)
      )

另一个查询

SELECT e.name, COUNT(a.`attendance_date`), COUNT(e.`name`) from tabEmployee e
LEFT JOIN tabAttendance a ON e.name = a.`employee`
WHERE a.`employee` IS NULL
GROUP BY e.`name`

推荐答案

使用LAG()解析函数，我们可以try :

WITH cte AS (
    SELECT e.name, e.full_name,
           LAG(a.attendance_date) OVER (PARTITION BY a.employee
                                        ORDER BY a.attendance_date) AS lag_attendance_date
    FROM tabAttendance a
    INNER JOIN tabEmployee e ON e.name = a.employee
)

SELECT DISTINCT name, full_name
FROM cte
WHERE DATEDIFF(attendance_date, lag_attendance_date) > 11;

这里的基本策略是在CTE中生成出勤日期的滞后(先前的顺序值).然后，我们只报告间隔11天或更长时间的员工.