Knowledge/1 使用/数据库/查找连续两天漏打卡的人.md

查找连续两天漏打卡的人

sql

复制

下载

-- 方法1：使用自连接 SELECT DISTINCT a.user_id FROM attendance a LEFT JOIN attendance b ON a.user_id = b.user_id AND b.attendance_date = DATE_ADD(a.attendance_date, INTERVAL 1 DAY) WHERE b.id IS NULL -- 找不到下一天的记录 AND a.attendance_date >= CURDATE() - INTERVAL 30 DAY;

-- 方法2：使用窗口函数（推荐，MySQL 8.0+） WITH attendance_gaps AS ( SELECT user_id, attendance_date, LEAD(attendance_date) OVER ( PARTITION BY user_id ORDER BY attendance_date ) as next_date FROM attendance WHERE attendance_date >= CURDATE() - INTERVAL 30 DAY ) SELECT DISTINCT user_id FROM attendance_gaps WHERE DATEDIFF(next_date, attendance_date) >= 2 OR (next_date IS NULL AND attendance_date = CURDATE() - INTERVAL 1 DAY);

-- 方法3：使用日期维度表 SELECT d.user_id FROM ( -- 生成用户所有应该打卡的日期 SELECT DISTINCT user_id, date_range.date FROM attendance CROSS JOIN ( SELECT CURDATE() - INTERVAL n DAY as date FROM (SELECT 0 AS n UNION SELECT 1 UNION SELECT 2) days ) date_range WHERE date_range.date BETWEEN CURDATE() - INTERVAL 30 DAY AND CURDATE() ) d LEFT JOIN attendance a ON d.user_id = a.user_id AND d.date = a.attendance_date GROUP BY d.user_id HAVING SUM(CASE WHEN a.id IS NULL THEN 1 ELSE 0 END) >= 2 AND MAX(d.date) - MIN(d.date) = 1; -- 连续两天