"SQL查詢特定日期範圍內有效的記錄"-PHP中文網路問答

我正在進行出勤控制的工作。簽到和簽退是表中的兩個不同記錄

有可能有人幾天沒簽退，但應該算當天出勤

人員ID - 簽到時間 - 簽下時間 3842 12/17/2022 09:030 -- 從未簽約（從12/17.2022之後的每一天都應計算在內） 3843 12/17/2022 08:00 -- 12/17/2022 09:30. （下面是同一天） 3843 12/17/2022 11:00 -- 12/17/2022 13:30. （上面是同一天） 3841 12/17/2022 08:00 -- 12/17/2022 17:45. （簡單同一天） 3844 12/17/2022 22:00 -- 12/18/2022 6:40. （跨越午夜-應計算12/17和12/18） 我期望的結果是 12/15 1人 12/17 4人 12/18 2人

我想知道X天有多少人出勤

我有點困惑如何處理兩個不同的記錄（簽到和簽退）或只有一個記錄可用（簽到）

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)

SELECT param.check_date, COUNT(DISTINCT p.id) FROM ( SELECT timestamp '2022-12-15 00:00' AS check_date UNION ALL SELECT timestamp '2022-12-17 00:00' AS check_date UNION ALL SELECT timestamp '2022-12-18 00:00' AS check_date ) AS param CROSS JOIN person AS p INNER JOIN my_history AS h ON h.person_id = p.id AND h.when_created >= COALESCE( ( SELECT when_created FROM my_history WHERE person_id = p.id AND when_created <= check_date ORDER BY when_created DESC LIMIT 1 ), check_date ) AND h.when_created < check_date + INTERVAL 1 DAY AND h.action = 'checked_in' GROUP BY param.check_date