mysql - 如何总结没有周末的休假时间

在这个例子中，在mysql数据库中，我在“leave”表中插入了新的leave：

+--------+---------+---------+-------------+----------+--------------------------
|ID_LEAVE|ID_WORKER| FNAME   | LNAME | BEGIN_DATE         | END_DATE            |
+--------+---------+---------+---------+-------------+--------------------+------
| 5      |   10    | MARIO   | NEED  |2019-03-22 07:00:00 |2019-03-25 15:00:00  |
+--------+---------+---------+-------------+----------+--------------------------

当我在下面的mysql查询中计算休假时间时：

SELECT leave.ID_LEAVE,
leave.ID_WORKER,
leave.BEGIN_DATE,
leave.END_DATE,
time_format(SUM((datediff(leave.END_DATE, leave.BEGIN_DATE) + 1) * (time(leave.END_DATE) - time(leave.BEGIN_DATE))), '%H:%i:%s') AS 'LEAVE TIME'
FROM leave
GROUP BY leave.ID_LEAVE

i have result请假时间=32:00:00
但我知道周末（星期六和星期天）也算。我不知道如果没有周末我该怎么改变。在这种情况下，请假时间应该是16:00:00。有人能问一下我能改什么样的问题吗。谢谢你的问候。:)

最佳答案

使用日历表（based on this solution）可以使用以下解决方案：

SELECT ID_LEAVE, SEC_TO_TIME(SUM(TIME_TO_SEC(TIMEDIFF(TIME(end_date), TIME(begin_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)
WHERE NOT WEEKDAY(date_value) IN (5, 6)
GROUP BY ID_LEAVE

demo on db-fiddle.com