文档建议反馈控制台
首页
学习
活动
专区
工具
TVP
最新优惠活动
文章/答案/技术大牛
发布
社区首页 >问答首页 >如何总结无周末休假时间

问如何总结无周末休假时间
EN

Stack Overflow用户
提问于 2019-04-10 01:20:06
回答 3查看 119关注 0票数 2

在本例中，在mysql数据库中，我在" leave“表中插入了新的leave：

代码语言：javascript
复制
+--------+---------+---------+-------------+----------+--------------------------
|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查询中总结休假时间时：

代码语言：javascript
复制
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 reasult TIME = 32:00:00

但我认为周末(周六和周日)也算在内。如果没有周末，我不知道该如何改变。在这种情况下，休假时间应为16:00:00。请问我可以更改哪种类型的查询？谢谢你的帮助。:)

mysql
datetime
sum
datediff
EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2019-04-12 14:59:39

您可以使用以下使用日历表格(based on this solution)的解决方案：

代码语言：javascript
复制
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
票数 2
EN

Stack Overflow用户

发布于 2019-04-10 10:59:23

这个@Prochu1991很有挑战性，但我想我可以为你构造一个查询。

编辑：下面的查询在某些情况下有一些问题。因此，我不建议使用它，但我将它留在这里，以防您可以做些什么：

代码语言：javascript
复制
-- Query 6: Final calculation add SUM of total leave time GROUP BY ID_LEAVE,ID_WORKER.
SELECT ID_LEAVE,ID_WORKER,BEGIN_DATE,END_DATE,
SEC_TO_TIME(SUM(TIME_TO_SEC(leave_TIME))) AS 'LEAVE TIME' 
FROM (
Query 5: Calculating leave time on each date only if VALID_LEAVE_DATES=1.
SELECT ID_LEAVE,ID_WORKER,BEGIN_DATE,END_DATE,
 IF(VALID_LEAVE_DATES=1,SEC_TO_TIME(TIME_TO_SEC(TIME(end_date))-TIME_TO_SEC(TIME(begin_date))),0) AS 'LEAVE_TIME' 
FROM (
-- Query 4: Add checking' if any of the dates are in the weekend, it will be set as 0.
SELECT leave_dates,
IF(DAYNAME(LEAVE_DATES) IN ('Saturday','Sunday'),0,1) AS 'VALID_LEAVE_DATES',
ID_LEAVE,ID_WORKER,BEGIN_DATE,END_DATE FROM (
-- Query 3: In this part, the main reason is to create dates between BEGIN_DATE and END_DATE.
SELECT ID_LEAVE,ID_WORKER,BEGIN_DATE,END_DATE,
-- concatenating extracted year-month with days generated from Query 1.
CONCAT_WS('-',DATE_FORMAT(BEGIN_DATE, '%Y-%m'),LPAD(days,2,0)) AS 'LEAVE_DATES' FROM
-- Query 1: This part is creating day value directly from query. If you run this individually, you'll get a day value from 0 to 39.
(SELECT 1 AS 'id'
a+b AS 'days' FROM
(SELECT 0 a 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) a,
(SELECT 0 b UNION SELECT 10 UNION SELECT 20 UNION SELECT 30) dd
-- Query 1 end here.
) ee 
LEFT JOIN 
-- Query 2: This is your original query. I removed the SUM in select.
(SELECT 1 AS 'id',
leave.ID_LEAVE, 
leave.ID_WORKER, 
leave.BEGIN_DATE, 
leave.END_DATE
FROM leave GROUP BY leave.ID_LEAVE) cd 
-- Query 2 end here.
ON ee.id=cd.id 
WHERE days BETWEEN DAY(BEGIN_DATE) AND DAY(END_DATE) -- `WHERE` condition only take date value between BEGIN_DATE and END_DATE from Query 2.
ORDER BY LEAVE_DATES) LCALC -- Query 3 end here.
) vvv GROUP BY ID_LEAVE,LEAVE_DATES -- Query 4 end here.
) tuv -- Query 5 end here.
GROUP BY ID_LEAVE,ID_WORKER; -- Query 6 end here.

希望你能理解我的解释。我将继续使用这个查询，看看是否有方法可以减少一些进程(减少查询)。

EDIT 2:好的，我一直在做这个@Prochu1991：

代码语言：javascript
复制
SELECT *,IF(valid_leave_days=0, TIMEDIFF(end_date,begin_date),
-- Assuming that normal working hours is '08:00:00'. If more, you just need to change here.
SEC_TO_TIME(TIME_TO_SEC('08:00:00')*Valid_leave_days)) AS 'Total_leave_time' 
-- So I convert 8 hours to seconds multiply with valid_leave_days calculated and convert it back to time. I think you understand this part.
FROM
(SELECT *,
-- This part where the CASE start is actually just determining how many leave days per person. 
-- Then minus with the total of weekend per week (sat & sun = 2 days).
CASE 
WHEN datedif<6 THEN datedif --if leave days are less than 6 days, it return datedif.
WHEN datedif=6 THEN datedif-1 --if leave days=6, datedif-1 day > because in any day you start you will surely get one weekend.
WHEN datedif BETWEEN 7 AND 12 THEN datedif-2 --if leave days between 7 and 12, datedif-2.
WHEN datedif=13 THEN datedif-3 -- from here you should get the idea.
WHEN datedif BETWEEN 14 AND 19 THEN datedif-4
WHEN datedif=20 THEN datedif-5
WHEN datedif BETWEEN 21 AND 26 THEN datedif-6
WHEN datedif=27 THEN datedif-7
WHEN datedif BETWEEN 28 AND 34 THEN datedif-8 
-- Note that this is only up to 34 days. if you want to add more days, just make sure the calculation is correct.
END AS 'Valid_leave_days' 
FROM
(SELECT *,DATEDIFF(end_date,begin_date) AS 'datedif' FROM LEAVE) a) b;
票数 0
EN

Stack Overflow用户

发布于 2019-04-12 08:29:59

对不起，我发布了另一个答案。你能试试这个吗？它对上面的第二个查询进行了修改，检查了begin_date：

代码语言：javascript
复制
SELECT *,TIMEDIFF(end_date,begin_date),IF(valid_leave_days2=0, TIMEDIFF(end_date,begin_date),SEC_TO_TIME(TIME_TO_SEC('08:00:00')*Valid_leave_days2)) AS 'Total_leave_time' FROM
(SELECT *,DAYNAME(begin_date),
CASE
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF=6 THEN datedif-1 
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF > 6 AND datedif < 13 THEN datedif-2 
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF=13 THEN datedif-3 
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF > 13 AND datedif < 20 THEN datedif-4 
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF=20 THEN datedif-5 
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF > 20 AND datedif < 27 THEN datedif-6
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF=27 THEN datedif-7 
WHEN DAYNAME(begin_date)='Monday' AND DATEDIF > 27 AND datedif < 34 THEN datedif-8 
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF=5 THEN datedif-1 
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF > 5 AND datedif < 12 THEN datedif-2 
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF=12 THEN datedif-3 
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF > 12 AND datedif < 19 THEN datedif-4 
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF=19 THEN datedif-5 
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF > 19 AND datedif < 26 THEN datedif-6
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF=26 THEN datedif-7 
WHEN DAYNAME(begin_date)='Tuesday' AND DATEDIF > 26 AND datedif < 33 THEN datedif-8 
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF=4 THEN datedif-1 
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF > 4 AND datedif < 11 THEN datedif-2 
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF=11 THEN datedif-3 
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF > 11 AND datedif < 18 THEN datedif-4 
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF=18 THEN datedif-5 
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF > 18 AND datedif < 25 THEN datedif-6
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF=25 THEN datedif-7 
WHEN DAYNAME(begin_date)='Wednesday' AND DATEDIF > 25 AND datedif < 32 THEN datedif-8 
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF=3 THEN datedif-1 
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF > 3 AND datedif < 10 THEN datedif-2 
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF=10 THEN datedif-3 
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF > 10 AND datedif < 17 THEN datedif-4 
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF=17 THEN datedif-5 
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF > 17 AND datedif < 24 THEN datedif-6
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF=24 THEN datedif-7 
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF > 24 AND datedif < 31 THEN datedif-8 
WHEN DAYNAME(begin_date)='Thursday' AND DATEDIF=31 THEN datedif-9 
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF=3 THEN datedif-1 
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF > 3 AND datedif < 9 THEN datedif-2 
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF=9 THEN datedif-3 
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF > 9 AND datedif < 16 THEN datedif-4 
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF=16 THEN datedif-5 
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF > 16 AND datedif < 23 THEN datedif-6
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF=23 THEN datedif-7 
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF > 23 AND datedif < 30 THEN datedif-8 
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF=30 THEN datedif-9 
WHEN DAYNAME(begin_date)='Friday' AND DATEDIF > 30 AND datedif < 37 THEN datedif-10 
ELSE datedif END AS 'valid_leave_days2' FROM
(SELECT *,DATEDIFF(end_date,begin_date-INTERVAL 1 DAY) AS 'datedif' FROM LEAVE) a) b;

对于你的评论“但我不知道为什么:在case counts中，在case 2019-03-20 07:00:00 - 2019-03-21 15:00:00中，它计入08:00:00而不是16:00:00"，我发现DATEDIFF没有将BEGIN_DATE或END_DATE包括在计算中。假设在你的情况下，如果你做DATEDIFF(END_DATE,BEGIN_DATE)，它会像这样计算，END_DATE-BEGIN_DATE所以21/03-20/03它只有1天！哦，天哪，我也是这么想的。我已经检查过MySQL是否有像DATE_COUNT这样的功能，但它没有。因此，我在添加DATEDIFF(end_date,begin_date-INTERVAL 1 DAY) AS 'datedif'的底部查询中做了一点修改。因此，- INTERVAL 1 DAY使函数从BEGIN_DATE开始计算天数。

P/S:你也可以像这样做DATEDIFF(end_date + INTERVAL 1 DAY,begin_date) AS 'datedif'。

EDIT:这些是我通过运行上面的查询获得的测试数据结果。

代码语言：javascript
复制
+------------+-------------+-----------------------+-----------------------+-----------+-----------------------+---------------------+---------------------------------+--------------------+
| "ID_LEAVE" | "ID_WORKER" |     "BEGIN_DATE"      |      "END_DATE"       | "datedif" | "DAYNAME(begin_date)" | "valid_leave_days2" | "TIMEDIFF(end_date,begin_date)" | "Total_leave_time" |
+------------+-------------+-----------------------+-----------------------+-----------+-----------------------+---------------------+---------------------------------+--------------------+
| "3"        | "26"        | "2019-03-20 07:00:00" | "2019-04-01 15:00:00" | "13"      | "Wednesday"           | "9"                 | "296:00:00"                     | "72:00:00"         |
| "4"        | "22"        | "2019-03-20 07:00:00" | "2019-03-20 15:00:00" | "1"       | "Wednesday"           | "1"                 | "08:00:00"                      | "08:00:00"         |
| "5"        | "27"        | "2019-03-01 07:00:00" | "2019-03-31 15:00:00" | "31"      | "Friday"              | "21"                | "728:00:00"                     | "168:00:00"        |
| "6"        | "28"        | "2019-03-22 07:00:00" | "2019-03-31 15:00:00" | "10"      | "Friday"              | "6"                 | "224:00:00"                     | "48:00:00"         |
| "7"        | "29"        | "2019-03-20 07:00:00" | "2019-03-21 15:00:00" | "2"       | "Wednesday"           | "2"                 | "32:00:00"                      | "16:00:00"         |
| "8"        | "30"        | "2019-03-20 07:00:00" | "2019-03-22 15:00:00" | "3"       | "Wednesday"           | "3"                 | "56:00:00"                      | "24:00:00"         |
| "9"        | "31"        | "2019-03-28 07:00:00" | "2019-04-01 15:00:00" | "5"       | "Thursday"            | "3"                 | "104:00:00"                     | "24:00:00"         |
+------------+-------------+-----------------------+-----------------------+-----------+-----------------------+---------------------+---------------------------------+--------------------+
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/55598108

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2024 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 深公网安备号 44030502008569

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号 | 京公网安备号11010802020287

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档