<p>我正在进行出勤控制的工作。签到和签退是表中的两个不同记录</strong></p> <p>有可能有人几天没有签退，但应该算作当天出勤</p> <pre class="brush:php;toolbar:false;">人员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人</pre> <p>我想知道X天有多少人出勤</p> <p>我有点困惑如何处理两个不同的记录（签到和签退）或者只有一个记录可用（签到）</p> <pre class="brush:php;toolbar:false;">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;</pre> <p>一些插入语句</p> <pre class="brush:php;toolbar:false;">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)</pre></p>

WHERE checkin <= '2022-12-14' and
      checkout >= '2022-12-14'

SELECT *
FROM my_history
WHERE checkin <= '2022-12-14' and
      COALESCE(checkout,'2022-12-14') >= '2022-12-14'

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