如何获取两个日期范围内的天数？

Question

我有一个 日历 表，其中包含 2021 年 12 月的所有日期（我只会举例说明表中的一些日期，但据了解，它实际上包含该月的所有日期）：

ID	日期
01	2021-12-01
02	2021-12-02
03	2021-12-03
04	2021-12-04
05	2021-12-05

我有users表：

ID	name	num_employee
01	Andrew	101
02	Mary	102

我有表assistances

ID	date	num_employee
01	2021-12-03	101
02	2021-12-04	101
03	2021-12-03	102
04	2021-12-04	102
05	2021-12-05	101
06	2021-12-06	102

我有一个查询来显示员工编号、姓名、出勤天数和缺勤天数：

SELECT u.num_employee,
       u.name,
       a.date AS attendances,
        (SELECT GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS date FROM calendar
           WHERE date BETWEEN '2021-12-01' AND '2021-12-31'
           AND NOT FIND_IN_SET(EXTRACT(DAY FROM date),a.date)) as faults FROM users u
JOIN (SELECT num_employee,
      GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS date FROM attendances
      WHERE date BETWEEN '2021-12-01' AND '2021-12-31'
      GROUP BY num_employee) a ON a.num_employee = u.num_employee

通过上述查询，我​​得到：

num_employee	name	attends	failure
101	安德鲁	3,4,5	1,2,3,6,7,8,9,10...
102	玛丽	3,4,6	1,2,5,7,8,9,10...

现在，重点是，除了出勤表之外，我还必须考虑另一个名为假期的表。该表的结构如下：

id	initial_date	Final_date	num_employee
01	2021-12-07	2021-12-09	101
02	2021-12-07	2021-12-09	102

考虑到该表，范围内的天数处理为假期应该停止出现在“故障”列中。结果应如下：

num_employee	name	attends	failure
101	Andrew	3,4,5	1,2,3,6,10...
102	Mary	3,4,6	1,2,5,10...

我怎样才能调整我的查询以获得上述内容？

考虑到我正在使用的 MariaDB 版本，有问题的查询无法适应使用 CTE。我正在开发 phpMyAdmin。

Answer 1

由于 MySQL 和 MariaDB 不擅长处理序列，因此最好有一个日历表来运行。

因此需要另一个子查询来检索假期日期。

我在子选择中使用了GROUP BY，因为一个月内可能有超过 1 个假期。

选择 u.num_employee,
       你的名字，
       a.AS 出席日期，
        (SELECT GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS 日历中的日期
           “2021-12-01”和“2021-12-31”之间的日期
           AND NOT FIND_IN_SET(EXTRACT(DAY FROM date),a.date)
           AND NOT FIND_IN_SET(EXTRACT(DAY FROM date),vac.vac_days)) 作为错误 
来自用户 u
左连接（选择 num_employee，
      GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS 出勤日期
      “2021-12-01”和“2021-12-31”之间的日期
      GROUP BY num_employee) a ON a.num_employee = u.num_employee
LEFT JOIN (SELECT v.`num_employee`, GROUP_CONCAT(DAY(c.`date`)) vac_days
FROM 假期 v INNER JOIN 日历 c ON c.`date` BETWEEN v.`initial_date` AND `final_date`
和c。 “2021-12-01”和“2021-12-31”之间的日期
GROUP BY v.`num_employee`) vac ON vac.`num_employee` = u.num_employee

<前>num_employee |名称 |出席人数 |故障
------------： | :----- | :---------- | :----------
101 | 101安德鲁 | 3,4,5 | 1,2,6,10,11
102 | 102玛丽| 3,4,6 | 1,2,5,10,11

db<>fiddle 此处

FIND_IN_SET 需要一个字符串，不能与 NULL 一起使用，所以你需要检查 NULL 值并替换它们

SELECT u.num_employee,
       你的名字，
       a.AS 出席日期，
        (SELECT GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS 日历中的日期
           “2021-12-01”和“2021-12-31”之间的日期
           AND NOT FIND_IN_SET(EXTRACT(DAY FROM date),IFNULL(a.date,''))
           AND NOT FIND_IN_SET(EXTRACT(DAY FROM date),IFNULL(vac.vac_days,''))
           ) 作为故障 
来自用户 u
左连接（选择 num_employee，
      GROUP_CONCAT(DISTINCT EXTRACT(DAY FROM date)) AS 出勤日期
      “2021-12-01”和“2021-12-31”之间的日期
      GROUP BY num_employee) a ON a.num_employee = u.num_employee
LEFT JOIN (SELECT v.`num_employee`, GROUP_CONCAT(DAY(c.`date`)) vac_days
FROM 假期 v INNER JOIN 日历 c ON c.`date` BETWEEN v.`initial_date` AND `final_date`
和c。 “2021-12-01”和“2021-12-31”之间的日期
GROUP BY v.`num_employee`) vac ON vac.`num_employee` = u.num_employee

<前>num_employee |名称 |出席人数 |故障
------------： | :----- | :---------- | :----------------
101 | 101安德鲁 | 3,4,5 | 1,2,6,10,11
102 | 102玛丽| 3,4,6 | 1,2,5,7,8,9,10,11

db<>fiddle 此处