基于SQL统计特定日期人员出勤情况（含跨天/未签出场景）

我正在开发出勤管控系统，签到（checked_in）和签退（checked_out）是my_history表中的独立记录。需要统计指定日期X的出勤人数，需覆盖以下场景：

• 人员签到后未签退：从签到日起，之后每日都计为出勤
• 同天多次签到签退：仅计为当日1人次出勤
• 跨天签到签退（比如17日晚签到，18日早签退）：签到日和签退日均计为出勤

示例数据对应的期望统计结果：

12/15 1人
12/17 4人
12/18 2人

表结构与测试数据 #

CREATE TABLE `my_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `person_id` int(11) DEFAULT NULL,
  `action` varchar(24) DEFAULT NULL,
  `when_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

INSERT INTO `my_history` ( `person_id`, `action`, `when_created`)
VALUES
    ( 3842, 'checked_in', '2022-12-15 08:00:00'),
    ( 3842, 'checked_out', '2022-12-15 09:30:00'),
    ( 3842, 'checked_in', '2022-12-17 09:30:00'),
    ( 3843, 'checked_in', '2022-12-17 08:00:00'),
    ( 3843, 'checked_out', '2022-12-17 09:30:00'),
    ( 3843, 'checked_in', '2022-12-17 11:00:00'),
    ( 3843, 'checked_out', '2022-12-17 13:30:00'),
    ( 3841, 'checked_in', '2022-12-17 08:00:00'),
    ( 3841,  'checked_out', '2022-12-17 17:42:00'),
    ( 3844, 'checked_in', '2022-12-17 22:00:00'),
    ( 3844,  'checked_out', '2022-12-18 06:40:00');

CREATE TABLE person (
  id    INT(11)
);

INSERT INTO person VALUES (3841), (3842), (3843), (3844);

解决方案SQL #

统计指定单日期的出勤人数 #

将'2022-12-17'替换为你需要统计的目标日期即可：

SELECT 
    DATE_FORMAT('2022-12-17', '%m/%d') AS date,
    COUNT(DISTINCT p.id) AS attendance_count
FROM 
    person p
LEFT JOIN (
    -- 为每条签到匹配后续最早的签退记录，避免同天多次签到重复统计
    SELECT 
        h_in.person_id,
        h_in.when_created AS check_in_time,
        MIN(h_out.when_created) AS check_out_time
    FROM 
        my_history h_in
    LEFT JOIN 
        my_history h_out 
        ON h_in.person_id = h_out.person_id 
        AND h_out.action = 'checked_out' 
        AND h_out.when_created > h_in.when_created
    WHERE 
        h_in.action = 'checked_in'
    GROUP BY 
        h_in.person_id, h_in.when_created
) AS attendance_periods ON p.id = attendance_periods.person_id
WHERE 
    -- 出勤判定：签到时间≤统计日结束，且（签退时间≥统计日开始 或 无签退记录）
    (attendance_periods.check_in_time <= '2022-12-17 23:59:59' 
     AND (attendance_periods.check_out_time >= '2022-12-17 00:00:00' 
          OR attendance_periods.check_out_time IS NULL))
GROUP BY 
    DATE_FORMAT('2022-12-17', '%m/%d');

批量统计多日期的出勤人数 #

如果需要统计连续日期的出勤情况，可生成日期序列后关联查询：

-- 生成目标日期范围（这里以2022-12-15至2022-12-18为例）
WITH date_range AS (
    SELECT '2022-12-15' AS stat_date UNION ALL
    SELECT '2022-12-16' UNION ALL
    SELECT '2022-12-17' UNION ALL
    SELECT '2022-12-18'
)
SELECT 
    DATE_FORMAT(d.stat_date, '%m/%d') AS date,
    COUNT(DISTINCT p.id) AS attendance_count
FROM 
    date_range d
CROSS JOIN 
    person p
LEFT JOIN (
    SELECT 
        h_in.person_id,
        h_in.when_created AS check_in_time,
        MIN(h_out.when_created) AS check_out_time
    FROM 
        my_history h_in
    LEFT JOIN 
        my_history h_out 
        ON h_in.person_id = h_out.person_id 
        AND h_out.action = 'checked_out' 
        AND h_out.when_created > h_in.when_created
    WHERE 
        h_in.action = 'checked_in'
    GROUP BY 
        h_in.person_id, h_in.when_created
) AS attendance_periods ON p.id = attendance_periods.person_id
WHERE 
    (attendance_periods.check_in_time <= CONCAT(d.stat_date, ' 23:59:59') 
     AND (attendance_periods.check_out_time >= CONCAT(d.stat_date, ' 00:00:00') 
          OR attendance_periods.check_out_time IS NULL))
GROUP BY 
    d.stat_date
ORDER BY 
    d.stat_date;

逻辑说明 #

1. 匹配签到签退对：通过自连接my_history表，为每条签到记录匹配后续最早的签退记录，确保同天多次签到签退不会重复统计。
2. 出勤判定条件：
   • 签到时间必须早于或等于统计日期的结束时间
   • 签退时间必须晚于或等于统计日期的开始时间，或者没有签退记录（说明该人员持续出勤）
3. 去重统计：用COUNT(DISTINCT p.id)确保同一人员在同一天无论有多少签到签退记录，仅计为1人次。

内容的提问来源于stack exchange，提问作者Jens H