"SQL查询特定日期范围内有效的记录"

Question

我正在进行出勤控制的工作。签到和签退是表中的两个不同记录

有可能有人几天没有签退，但应该算作当天出勤

人员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)

Answer 1

通常，要测试这种情况，您需要查看您感兴趣的日期是否大于入住日期并小于退房日期。例如

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

为了解决退房日期可以为空但仍然“OK”的情况，我们只需用满足条件的值替换空值。

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

Answer 2

由于要求变更，已删除旧答案。匆忙之中，没有时间解释。

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

https://dbfiddle.uk/RXx0x9xt