mysql - 如何总结无公休日的休假？

Question

大家好(假期前)。

在这种情况下，我在此表中添加了新的休假:

+--------+---------+---------+-------------+----------+--------------------------
|ID_LEAVE|ID_WORKER| FNAME   | LNAME | BEGIN_DATE         | END_DATE            | 
+--------+---------+---------+---------+-------------+--------------------+------
| 8      |   10    | MARIO   | NEED  |2019-04-22 07:00:00 |2019-04-23 15:00:00  | 
+--------+---------+---------+-------------+----------+-------------------------- 

但我知道假期是这样表示的:

我已经做了什么？

我已经完成了该查询，该查询汇总了按 ID_LEAVE 分组的所有员工的休假时间

SELECT leave.ID_LEAVE, leave.ID_WORKER, workers.FNAME, workers.LNAME, leave.BEGIN_DATE, leave.END_DATE,  
FROM 
(SELECT ADDDATE('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) AS date_value 
 FROM 
(SELECT 0 t0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t0, 
 (SELECT 0 t1 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t1, 
 (SELECT 0 t2 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t2, 
 (SELECT 0 t3 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t3, 
 (SELECT 0 t4 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) t4) calendar 
INNER JOIN leave ON calendar.date_value BETWEEN DATE(leave.BEGIN_DATE) AND DATE(leave.END_DATE) 
INNER JOIN workers ON leave.ID_WORKER = workers.ID_WORKER 
WHERE NOT WEEKDAY(date_value) IN (5, 6)
GROUP BY ID_LEAVE;

现在我想要一个查询，该查询总结了所有员工的休假时间，但没有我在上图中表示的假期。我应该做什么？我应该创建新的“假期”表，然后从此表下载该日期还是在上面的查询中添加日期？

Answer 1

如果您有一个日历表，其中列出了所有日期(不仅是假期，还列出了接下来 X 年的每一天，以及一列表明该天是否为公共(public)假期等)，那么您的查询如下所示

SELECT l.id_leave, count(*)
FROM calendar c INNER JOIN leave l ON c.calendar_date BETWEEN l.start_date AND l.end_date
WHERE c.day_type NOT IN ('publicholiday', 'weekend')
GROUP BY l.id_leave

您可以添加更多的复杂性，但这是核心。不知道为什么你的休假从早上 7 点开始，晚上 10 点结束，但如果你允许小数天休假，你可能需要添加一些逻辑 - 将开始日期延长到午夜(这样中间的时间就可以正常工作)，然后使用一个案例来说明何时休假根据所考虑的日期是开始日期、结束日期还是中间日期构建分数