<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