48 lines
1.4 KiB
Markdown
48 lines
1.4 KiB
Markdown
#### **查找连续两天漏打卡的人**
|
|
|
|
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; -- 连续两天 |