如何将“疾病时间”sumarize到命令结束 - mysql [英] How to sumarize “sickness time” to the end of order - mysql
问题描述
在这种情况下我有病表：
In that case i have "sickness" table:
+--------+---------+---------+-------------+---------+-------------------------------+---------------+
| Id_SICK|ID_WORKER| FNAME | LNAME | BEGIN_DATE | END_DATE | SICKNESS_TIME |
+--------+---------+---------+---------+------------+--------------------+-----------+---------------+
| 6 | 17 | PAUL | KING |2019-03-19 07:00:00 |2019-03-20 15:00:00 | 16:00:00 |
| 7 | 17 | PAUL | KING |2019-03-25 07:00:00 |2019-03-25 15:00:00 | 8:00:00 |
+--------+---------+---------+----------------------+--------------------------------+---------------+
工人表：
"Workers" table:
+----------+---------+---------+
|ID_WORKER | FNAME | LNAME |
+----------+---------+----------
| 17 | PAUL | KING |
| 18 | SAM | BULK |
+----------+---------+---------+
订单表：
"Orders" table:
+----------+--------------+---------------+
|ID_ORDER | DESC_ORDER | NUMBER_ORDER |
+----------+--------------+---------------+
| 20 | TEST | TEST |
+----------+--------------+---------------+
Order_status表：
"Order_status" table:
+----------+---------+---------+---------------------+-------------------+------------+
| Id_status|ID_WORKER| ID_ORDER| BEGIN_DATE | END_DATE | ORDER_DONE |
+----------+---------+---------+----------+------------+---------+--------------------+
| 47 | 17 | 20 |2019-03-18 06:50:35 |2019-03-18 15:21:32| NO |
| 48 | 17 | 20 |2019-03-20 06:44:12 |2019-03-20 15:11:23| NO |
| 50 | 17 | 20 |2019-03-22 06:50:20 |2019-03-22 12:22:33| YES |
| 51 | 18 | 20 |2019-03-18 06:45:11 |2019-03-18 15:14:45| NO |
| 52 | 18 | 20 |2019-03-20 06:50:22 |2019-03-20 15:10:32| NO |
| 53 | 18 | 20 |2019-03-22 06:54:11 |2019-03-22 11:23:45| YES |
+----------+---------+---------+------------+---------+-------------------+-----------+
我做了什么：
我可以对每个其他工人的总时间进行sumarize（在订单上的order_status表）包括来自Sickness表的sumarizing疾病时间。我已经正确选择了工人（LNAME，FNAME）订单（DESC_ORDER和NUMBER_ORDER）和TOTAL TIME。我在下面编写了mysql命令：
What i've done:
I can to sumarize "total time" of each other workers (in order_status table) on the order including with sumarizing "sickness time" from Sickness table. I have selected workers (LNAME, FNAME) orders (DESC_ORDER and NUMBER_ORDER) and "TOTAL TIME" on order from each other workers correctly too. I wrote the mysql command in below:
SELECT workers.fname,
workers.lname,
order_statusAgg.number_order,
workers.id_worker,
order_statusAgg.desc_order,
SEC_TO_TIME(SUM(order_statusAgg.stime)) AS 'TOTAL TIME',
IFNULL(SEC_TO_TIME(SUM(sickAgg.vtime)),'00:00:00') AS 'LEAVE TIME'
FROM workers
LEFT JOIN (
SELECT leave.id_worker, SUM((datediff(sickness.end_date, sickness.begin_date) + 1) * (time_to_sec(time(sickness.end_date)) - time_to_sec(time(sickness.begin_date)))) AS vtime
FROM sickness
GROUP BY sickness.id_worker) sickAgg
ON sickAgg.id_worker = workers.id_worker
LEFT JOIN (
SELECT order_status.id_worker, orders.number_order, orders.desc_order,
SUM((Time_to_sec(order_status.end_date) -
Time_to_sec(order_status.begin_date))) AS stime
FROM order_status
INNER JOIN orders
ON orders.id_order = order_status.id_order
GROUP BY order_status.id_worker) order_statusAgg
ON workers.id_worker = order_statusAgg.id_worker
WHERE order_statusAgg.number_order LIKE 'TEST'
GROUP BY workers.id_worker;
然后我得到：
Then i get:
+---------+---------+---------------+------------+------------+--------------+
| FNAME | LNAME | NUMBER_ORDER | DESC_ORDER | TOTAL TIME | Sickness_time|
+---------+---------+---------------+------------+------------+--------------+
| PAUL | KING | TEST | TEST | 22:30:21 | 24:00:00 |
| SAM | BULK | TEST | TEST | 21:19:18 | 00:00:00 |
+---------+---------+---------------+------------+------------+--------------+
Okey但另一方面该订单于23-03-2019完成。保罗·金在25-03-2019也有病，他的病情时间不应该在他正在做的这个命令中加上。所以在这种情况下应该是：
Okey but on the other hand that order was finished in 23-03-2019. PAUL KING had a sickness in 25-03-2019 too and his sickness time shouldn't added during this order which he was doing. So in this case that should be:
+---------+---------+---------------+------------+------------+--------------+
| FNAME | LNAME | NUMBER_ORDER | DESC_ORDER | TOTAL TIME | Sickness_time|
+---------+---------+---------------+------------+------------+--------------+
| PAUL | KING | TEST | TEST | 22:30:21 | 16:00:00 |
| SAM | BULK | TEST | TEST | 21:19:18 | 00:00:00 |
+---------+---------+---------------+------------+------------+--------------+
我想知道这是否与代码问题有关？也许别的什么？
I'm wondering if that is about that code issue? Maybe something else?
LEFT JOIN (
SELECT leave.id_worker, SUM((datediff(sickness.end_date, sickness.begin_date) + 1) * (time_to_sec(time(sickness.end_date)) - time_to_sec(time(sickness.begin_date)))) AS vtime
FROM sickness
GROUP BY sickness.id_worker) sickAgg
ON sickAgg.id_worker = workers.id_worker
我尝试过：
有没有想法如何处理总结直到订单持续时间结束？这有可能吗？我正在寻找任何想法，但我不是mysql大师。感谢您的帮助。
What I have tried:
Has someone ideas how to deal with summarizing it till the end of duration of the order? Is it that possible? I was searching any idea but I'm not mysql guru. Thank you for any help.
推荐答案
您应该记录/生成每日时间记录以简化操作。
例如
day 1 7:00 - 15:00 xxx
day 2 7:00 - 12:00 xxx
day 2 12:00 - 15:00 sick
这样，通过选择在特定时段和工作单项目分配时间这是一件简单的事情具有特定日期范围的物品。
如果您只有生病记录，您可以反算您需要的东西。
You should be recording / generating "daily time records" to simplify things.
e.g.
day 1 7:00 - 15:00 xxx
day 2 7:00 - 12:00 xxx
day 2 12:00 - 15:00 sick
That way, it's a simple matter to "allocate time" over a particular period and work order items by selecting items that fall withing a particular date range.
You can "back calculate" what you need if you only have "sick records".
这篇关于如何将“疾病时间”sumarize到命令结束 - mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
