mysql - 如何根据另一个表中的两个日期字段获取一个表中的记录数

Question

如何根据另一个表中的两个日期字段获取一个表中的记录数

例如:
表 1:叶子

| leav_id     | from      | to             |


| 1           | 2015-10-20 | 2015-10-27    |

| 2           | 2015-11-10 | 2015-10-12    |

表 2:假期

| holi_id     | On         | Name       |


| 1           | 2015-12-25 | Christmas  |

我想获取 表叶中 from 和 to 之间的天数，不包括该范围内表 holiday 的假期计数。

Answer 1

这是你可以做的，我已经创建了更多的假期来进行计算

mysql> select * from leaves ;
+---------+------------+------------+
| leav_id | from       | to         |
+---------+------------+------------+
|       1 | 2015-10-20 | 2015-10-27 |
|       2 | 2015-12-20 | 2015-12-30 |
+---------+------------+------------+
2 rows in set (0.00 sec)

mysql> select * from holidays ;
+---------+------------+----------+
| holi_id | on         | name     |
+---------+------------+----------+
|       1 | 2015-12-25 | X-Mass   |
|       2 | 2015-12-26 | Saturday |
|       3 | 2015-12-26 | Sunday   |
|       4 | 2015-10-24 | Saturday |
|       5 | 2015-10-25 | Sunday   |
+---------+------------+----------+

所以下面的查询会给你不包括节假日的叶子总数

select 
t1.leav_id,
( 
  datediff(t1.`to`,t1.`from`) 
  - 
  sum(
    case when t2.`on` >= t1.`from` and t2.`on` <= t1.`to` 
    then 1 else 0  end
  ) 
) as total_leave
from  leaves t1 
left join holidays t2 on t2.`on` >= t1.`from` 
and t2.`on` <= t1.`to` 
group by t1.leav_id 

结果如下

+---------+-------------+
| leav_id | total_leave |
+---------+-------------+
|       1 |           5 |
|       2 |           7 |
+---------+-------------+

另请注意，datediff 函数可能无法为您提供正确的结果，例如

mysql> select datediff('2015-12-30','2015-12-20') as d ;
+------+
| d    |
+------+
|   10 |
+------+

这里是 10 天，但是当计算休假时是 11 天，即从 2015-12-20 到 2015-12-30

因此需要重构上述查询以将 +1 添加为

select 
t1.leav_id,
( 
  datediff(t1.`to`,t1.`from`) 
  - 
  sum(
    case when t2.`on` >= t1.`from` and t2.`on` <= t1.`to` 
    then 1 else 0 end
   ) 
 )+1 as total_leave
from  leaves t1 
left join holidays t2 on t2.`on` >= t1.`from` 
and t2.`on` <= t1.`to` 
group by t1.leav_id 

会给出

+---------+-------------+
| leav_id | total_leave |
+---------+-------------+
|       1 |           6 |
|       2 |           8 |
+---------+-------------+