获取连续11天没有打卡的员工

Question

我正在尝试从数据库中获取连续11天没有标记出勤的员工， 为此我有员工表和出勤表，但是我在这方面遇到的问题是出勤表中没有记录，所以我该如何获取

我尝试了很多查询，其中一些如下：

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`

Answer 1

使用 LAG() 分析函数，我们可以尝试：

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 中生成出勤日期的 lag（前一个连续值）。然后我们只报告有 11 天或更长间隔的员工。