如何按天筛选数据库行？求指定时段员工每日零缺勤零工时SQL查询方案

1. 按天筛选数据库行的方法 #

当然存在！核心思路是提取时间字段的日期部分，忽略时分秒的差异，然后基于日期进行筛选或分组。不同数据库的函数略有不同，举几个常见例子：

• MySQL/MariaDB：用DATE()函数，比如WHERE DATE(created_at) = '2024-05-20'
• PostgreSQL：用CAST(created_at AS DATE)或者created_at::DATE，比如WHERE created_at::DATE = '2024-05-20'
• SQL Server：用CAST(created_at AS DATE)，比如WHERE CAST(created_at AS DATE) = '2024-05-20'
• Oracle：用TRUNC(created_at)，比如WHERE TRUNC(created_at) = TO_DATE('2024-05-20', 'YYYY-MM-DD')

如果是要筛选某个日期范围内的所有行，直接用日期范围条件即可，比如WHERE created_at BETWEEN '2024-05-01' AND '2024-05-31 23:59:59'（注意部分数据库的闭区间处理细节）。

2. 实现指定时间区间内每日缺勤为0且工时为0的员工查询 #

要实现这个需求，关键是生成时间区间内的所有日期，然后逐个日期检查每个员工的缺勤和工时情况，最后筛选出符合条件的记录（或员工）。以下是详细的实现思路和示例SQL：

前提假设 #

先明确几个字段定义（如果你的字段名不同，替换成实际名称即可）：

• v_personnel：包含员工唯一标识，比如person_id、person_name
• exp_mat_abs：缺勤记录，包含person_id、abs_date（缺勤日期）、abs_hours（缺勤时长，若为0则无缺勤）
• exp_mat_mo：工时记录，包含person_id、work_date（工时日期）、work_hours（工时时长）
• 我们需要查询的时间区间：比如@start_date（开始日期）到@end_date（结束日期）

步骤1：生成时间区间内的所有日期 #

首先需要生成目标区间的每一天，这里用MySQL的递归CTE作为示例（其他数据库写法类似，比如PostgreSQL递归CTE、SQL Server的数字表等）：

WITH date_range AS (
    SELECT @start_date AS day
    UNION ALL
    SELECT DATE_ADD(day, INTERVAL 1 DAY)
    FROM date_range
    WHERE day < @end_date
)

步骤2：关联员工与日期，统计缺勤和工时 #

接下来，把所有员工和日期组合，左连接缺勤和工时表，统计每天的缺勤时长总和和工时总和：

WITH date_range AS (
    SELECT '2024-05-01' AS day  -- 替换成你的开始日期
    UNION ALL
    SELECT DATE_ADD(day, INTERVAL 1 DAY)
    FROM date_range
    WHERE day < '2024-05-10'  -- 替换成你的结束日期
)
SELECT
    p.person_id,
    p.person_name,
    dr.day,
    COALESCE(SUM(a.abs_hours), 0) AS total_absent_hours,
    COALESCE(SUM(m.work_hours), 0) AS total_work_hours
FROM date_range dr
CROSS JOIN v_personnel p
LEFT JOIN exp_mat_abs a ON p.person_id = a.person_id AND dr.day = DATE(a.abs_date)
LEFT JOIN exp_mat_mo m ON p.person_id = m.person_id AND dr.day = DATE(m.work_date)
GROUP BY p.person_id, p.person_name, dr.day

步骤3：筛选符合条件的记录 #

情况1：整个区间内每一天都符合条件的员工

如果需要找出在指定区间内，每一天都满足缺勤0且工时0的员工，可以在上述基础上再分组员工，检查所有日期的统计结果都符合条件：

WITH date_range AS (
    SELECT '2024-05-01' AS day
    UNION ALL
    SELECT DATE_ADD(day, INTERVAL 1 DAY)
    FROM date_range
    WHERE day < '2024-05-10'
),
daily_stats AS (
    SELECT
        p.person_id,
        p.person_name,
        dr.day,
        COALESCE(SUM(a.abs_hours), 0) AS total_absent_hours,
        COALESCE(SUM(m.work_hours), 0) AS total_work_hours
    FROM date_range dr
    CROSS JOIN v_personnel p
    LEFT JOIN exp_mat_abs a ON p.person_id = a.person_id AND dr.day = DATE(a.abs_date)
    LEFT JOIN exp_mat_mo m ON p.person_id = m.person_id AND dr.day = DATE(m.work_date)
    GROUP BY p.person_id, p.person_name, dr.day
)
SELECT person_id, person_name
FROM daily_stats
WHERE total_absent_hours = 0 AND total_work_hours = 0
GROUP BY person_id, person_name
HAVING COUNT(*) = (SELECT COUNT(*) FROM date_range)

情况2：区间内任意一天符合条件的员工及对应日期

如果只是需要获取区间内某一天满足条件的员工及该日期，直接在daily_stats后加筛选条件即可：

-- 接上面的daily_stats CTE
SELECT person_id, person_name, day
FROM daily_stats
WHERE total_absent_hours = 0 AND total_work_hours = 0

注意事项 #

• 如果你的缺勤表中，没有缺勤记录即表示缺勤为0，那么可以用COUNT(a.abs_id) = 0代替SUM(a.abs_hours) = 0，根据实际业务逻辑调整。
• 不同数据库生成日期序列的语法不同，比如PostgreSQL可以用generate_series(@start_date, @end_date, '1 day'::interval)，Oracle用SELECT TRUNC(@start_date) + LEVEL - 1 AS day FROM dual CONNECT BY TRUNC(@start_date) + LEVEL - 1 <= @end_date。
• 确保日期字段的索引，避免大数据量下查询过慢。

内容的提问来源于stack exchange，提问作者Quentin Sorin