sumarize如何将时间留给最大订单日期 - mysql [英] How sumarize leave time to max date of order - mysql

问题描述

在这种情况下，我创建了休假表：

 + -------- + ------ --- + --------- + ------------- + --------- + ------------ -------------- 
 | ID_LEAVE | ID_WORKER | FNAME | LNAME | BEGIN_DATE | END_DATE | 
 + -------- + --------- + --------- + --------- + ------ ------ + -------------------- + ------ 
 | 1 | 1 | ALAN | MAX | 2019-03-22 07:00:00 | 2019-03-23 15:00:00 | 
 | 2 | 1 | ALAN | MAX | 2019-03-21 07:00:00 | 2019-03-21 15:00:00 | 
 + -------- + --------- + --------- + ---------------- ------ + --------------------------- 

工人表

 + ---------- + ----- ---- + --------- + 
 | ID_WORKER | FNAME | LNAME | 
 + ---------- + --------- + ---------- 
 | 1 | ALAN | MAX | 
 | 2 | MARK |黑暗| 
 + ---------- + --------- + --------- + 

订单表：

 + ---------- +  - ------------ + --------------- + 
 | ID_ORDER | DESC_ORDER | NUMBER_ORDER | 
 + ---------- + -------------- + --------------- + 
 | 20 |测试|测试| 
 + ---------- + -------------- + --------------- + 

Order_status表：

 +  - -------- + --------- + --------- + --------------------- + ------------------- + ------------ + 
 | Id_status | ID_WORKER | ID_ORDER | BEGIN_DATE | END_DATE | ORDER_DONE | 
 + ---------- + --------- + --------- + ---------- + --- --------- + --------- + -------------------- + 
 | 30 | 1 | 20 | 2019-03-18 06:50:35 | 2019-03-18 15:21:32 |没有| 
 | 31 | 1 | 20 | 2019-03-20 06:44:12 | 2019-03-20 15:11:23 |没有| 
 | 32 | 1 | 20 | 2019-03-22 06:50:20 | 2019-03-22 12:22:33 |是的| 
 | 33 | 2 | 20 | 2019-03-18 06:45:11 | 2019-03-18 15:14:45 |没有| 
 | 34 | 2 | 20 | 2019-03-20 06:50:22 | 2019-03-20 15:10:32 |没有| 
 | 35 | 2 | 20 | 2019-03-22 06:54:11 | 2019-03-22 11:23:45 |是的| 
 + ---------- + --------- + --------- + ------------ +  - -------- + ------------------- + ----------- + 

我做了什么：



我可以对每个其他工人的总时间进行sumarize（在订单上的order_status表）包括来自离开表的sumarizing离开时间和sumarizing订购时间+离开时间。我已经正确选择了工人（LNAME，FNAME）订单（DESC_ORDER和NUMBER_ORDER）和TOTAL TIME。我在下面编写了mysql命令：

 SELECT workers.FNAME，
 workers.LNAME，
 order_statusAgg.NUMBER_ORDER ，
 order_statusAgg.DESC_ORDER，
 SEC_TO_TIME（SUM（order_statusAgg.stime））AS'Order Time'，
 IFNULL（SEC_TO_TIME（SUM（leaveAgg.ltime）），'00:00:00 '）AS'LEAVE TIME'，
 ADDTIME（SEC_TO_TIME（SUM（order_statusAgg.stime）），IFNULL（SEC_TO_TIME（SUM（leaveAgg.ltime）），'00：00：00'））AS'TOTAL TIME' 
 FROM workers 
 LEFT JOIN（SELECT leave.ID_WORKER，SUM（（datediff（leave.BEGIN_DATE，leave.END_DATE）+ 1）*（time_to_sec（time（leave.END_DATE）） -  time_to_sec（time（ leave.BEGIN_DATE））））AS ltime FROM离开
 LEFT JOIN 
（SELECT ID_WORKER，MIN（BEGIN_DATE）AS'MIN_BEGIN_DATE'，MAX（END_DATE）AS'MAX_END_DATE'
 FROM order_status GROUP BY ID_WORKER）ordstat ON 
 leave.ID_WORKER = ordstat.ID_WORKER 
 WHERE leave.END_DATE< = MAX_END_DATE AND leave.BEGIN_DATE> = MIN_BEGIN_DATE GROUP BY leave.ID_WORKER）leaveA gg 
 ON leaveAgg.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命令
 ON orders.ID_ORDER = order_status.ID_ORDER 
 GROUP BY order_status.ID_WORKER）order_statusAgg 
 ON工人。 ID_WORKER = order_statusAgg.ID_WORKER 
 WHERE order_statusAgg.NUMBER_ORDER LIKE'TEST'
 GROUP BY workers.ID_WORKER; 

然后我得到：

 + --------- + --------- + --- ------------ + ------------ + ------------ + ----------- --- + ----------- + 
 | FNAME | LNAME | NUMBER_ORDER | DESC_ORDER |订购时间| LEAVE_TIME |总时间| 
 + --------- + --------- + --------------- + --------- --- + ------------ + -------------- + ----------- + 
 | ALAN | MARK |测试|测试| 22:30:21 | 08:00:00 | 30:30:21 | 
 + --------- + --------- + --------------- + --------- --- + ------------ + -------------- + ----------- + 
 | MARK |绿色|测试|测试| 21:19:18 | 00:00:00 | 21:19:18 | 
 + --------- + --------- + --------------- + --------- --- + ------------ + -------------- + ----------- + 

ok。但请稍等：我给Allan Max加了假：



2019-03-19 07:00:00 | 2019-03-23 15:00:00

2019-03-21 07:00:00 | 2019-03-21 15:00:00



得到的只有8： 00:00的休假。因为这个订单于22-03-2019结束。我想得到（尽管在Allan Max离开

 2019-03-19 07:00:00 | 2019-03-23 15:00:00 

）结果：

 + --------- + --------- + ------ --------- + ------------ + ------------ + -------------- + ----------- + 
 | FNAME | LNAME | NUMBER_ORDER | DESC_ORDER |订购时间| LEAVE_TIME |总时间| 
 + --------- + --------- + --------------- + --------- --- + ------------ + -------------- + ----------- + 
 | ALAN | MARK |测试|测试| 22:30:21 | 16:00:00 | 38:30:21 | 
 + --------- + --------- + --------------- + --------- --- + ------------ + -------------- + ----------- + 
 | MARK |绿色|测试|测试| 21:19:18 | 00:00:00 | 21:19:18 | 
 + --------- + --------- + --------------- + --------- --- + ------------ + -------------- + ----------- + 

2019-03-22 07:00:00 | 2019-03-23 15:00:00（到2019-03-22结束） = 8:00:00（也许）2019-03-21 07:00:00 | 2019-03-21 15:00:00（到2019-03-22结束）= 8:00:00



所以LEAVE_TIME应该是8:00:00 + 8:00:00 = 16:00:00



可以改变它吗？有人可以帮我怎么做吗？提前感谢您的任何帮助或建议。



我尝试过：



我尝试过这个查询：

 SELECT workers.FNAME，
 workers.LNAME，
 order_statusAgg.NUMBER_ORDER，
 order_statusAgg.DESC_ORDER，
 SEC_TO_TIME（SUM（order_statusAgg.stime））AS'Order Time'，
 IFNULL（SEC_TO_TIME（SUM（leaveAgg.ltime）），'00 ：00：00'）AS'LEAVE TIME'，
 ADDTIME（SEC_TO_TIME（SUM（order_statusAgg.stime）），IFNULL（SEC_TO_TIME（SUM（leaveAgg.ltime）），'00：00：00'）AS 'TOTAL TIME'
 FROM workers 
 LEFT JOIN（SELECT leave.ID_WORKER，SUM（（datediff（leave.BEGIN_DATE，leave.END_DATE）+ 1）*（time_to_sec（time（leave.END_DATE）） - time_to_sec（time（leave.BEGIN_DATE））））AS ltime FROM leave 
 LEFT JOIN 
（SELECT ID_WORKER，MIN（BEGIN_DATE）AS'MIN_BEGIN_DATE'，MAX（END_DATE）AS'MAX_END_DATE'
 FROM order_status GROUP BY ID_WORKER）ordstat ON 
 leave.ID_WORKER = ordstat.ID_WORKER 
 WHE RE leave.END_DATE< = MAX_END_DATE AND leave.BEGIN_DATE> = MIN_BEGIN_DATE GROUP BY leave.ID_WORKER）leaveAgg 
 ON leaveAgg.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命令
 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; 

但遗憾的是我不知道如何改变它。

解决方案

1）你有ALAN工作和休假同时。

2）你把订单完成信息放在工人级别（两次）应该在订单水平

3）订单上的开始和结束日期将使其变得简单呃分配。你现在遇到问题，因为你的设计很薄弱。

 + ----- --- + --------- + --------- + ------------- + --------- +  - ------------------------ 
 | ID_LEAVE | ID_WORKER | FNAME | LNAME | BEGIN_DATE | END_DATE | 
 + -------- + --------- + --------- + --------- + ------ ------ + -------------------- + ------ 
 | 1 | 1 | ALAN | MAX | 2019-03-22 07:00:00 | 2019-03-23 15:00:00 | 
 + -------- + --------- + --------- + ---------------- ------ + --------------------------- 
 
 + ------- --- + --------- + --------- + --------------------- + ---- --------------- + ------------ + 
 | Id_status | ID_WORKER | ID_ORDER | BEGIN_DATE | END_DATE | ORDER_DONE | 
 + ---------- + --------- + --------- + ---------- + --- --------- + --------- + -------------------- + 
 | 
 | 32 | 1 | 20 | 2019-03-22 06:50:20 | 2019-03-22 12:22:33 |是的| 
 | 
 + ---------- + --------- + --------- + ------------ +  - -------- + ------------------- + ----------- + 

In this case i have created leave table:

+--------+---------+---------+-------------+---------+--------------------------
|ID_LEAVE|ID_WORKER| FNAME | LNAME | BEGIN_DATE         | END_DATE             | 
+--------+---------+---------+---------+------------+--------------------+------
| 1      |   1     | ALAN  | MAX   |2019-03-22 07:00:00 |2019-03-23 15:00:00   | 
| 2      |   1     | ALAN  | MAX   |2019-03-21 07:00:00 |2019-03-21 15:00:00   |
+--------+---------+---------+----------------------+---------------------------

"Workers" Table

+----------+---------+---------+
|ID_WORKER |  FNAME  | LNAME   |
+----------+---------+----------
| 1        |  ALAN   |  MAX    |
| 2        |  MARK   |  DARK   |
+----------+---------+---------+

"Orders" Table:

+----------+--------------+---------------+
|ID_ORDER  |  DESC_ORDER  | NUMBER_ORDER  |
+----------+--------------+---------------+
| 20       |  TEST        |  TEST         |
+----------+--------------+---------------+

"Order_status" Table:

+----------+---------+---------+---------------------+-------------------+------------+
| Id_status|ID_WORKER| ID_ORDER| BEGIN_DATE          | END_DATE          | ORDER_DONE |
+----------+---------+---------+----------+------------+---------+--------------------+
| 30       |   1     |    20   |2019-03-18 06:50:35  |2019-03-18 15:21:32|  NO        |
| 31       |   1     |    20   |2019-03-20 06:44:12  |2019-03-20 15:11:23|  NO        |
| 32       |   1     |    20   |2019-03-22 06:50:20  |2019-03-22 12:22:33|  YES       |
| 33       |   2     |    20   |2019-03-18 06:45:11  |2019-03-18 15:14:45|  NO        |
| 34       |   2     |    20   |2019-03-20 06:50:22  |2019-03-20 15:10:32|  NO        |
| 35       |   2     |    20   |2019-03-22 06:54:11  |2019-03-22 11:23:45|  YES       |
+----------+---------+---------+------------+---------+-------------------+-----------+ 

What i've done:

I can to sumarize "total time" of each other workers (in order_status table) on the order including with sumarizing "leave time" from Leave table and sumarizing "Order time" + "leave time". 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,
order_statusAgg.DESC_ORDER, 
SEC_TO_TIME(SUM(order_statusAgg.stime)) AS 'ORDER TIME', 
IFNULL(SEC_TO_TIME(SUM(leaveAgg.ltime)),'00:00:00') AS 'LEAVE TIME',
ADDTIME(SEC_TO_TIME(SUM(order_statusAgg.stime)), IFNULL(SEC_TO_TIME(SUM(leaveAgg.ltime)), '00:00:00')) AS 'TOTAL TIME'
FROM workers
LEFT JOIN (SELECT leave.ID_WORKER, SUM((datediff(leave.BEGIN_DATE, leave.END_DATE) + 1) * (time_to_sec(time(leave.END_DATE)) - time_to_sec(time(leave.BEGIN_DATE)))) AS ltime FROM leave
LEFT JOIN
(SELECT ID_WORKER, MIN(BEGIN_DATE) AS 'MIN_BEGIN_DATE', MAX(END_DATE) AS 'MAX_END_DATE' 
FROM order_status GROUP BY ID_WORKER) ordstat ON 
leave.ID_WORKER = ordstat.ID_WORKER 
WHERE leave.END_DATE <= MAX_END_DATE AND leave.BEGIN_DATE >= MIN_BEGIN_DATE GROUP BY leave.ID_WORKER) leaveAgg
ON leaveAgg.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 | ORDER TIME | LEAVE_TIME   | TOTAL TIME|
+---------+---------+---------------+------------+------------+--------------+-----------+
|  ALAN   |  MARK   | TEST          | TEST       | 22:30:21   |   08:00:00   |  30:30:21 |
+---------+---------+---------------+------------+------------+--------------+-----------+
|  MARK   |  GREEN  | TEST          | TEST       | 21:19:18   |   00:00:00   |  21:19:18 |
+---------+---------+---------------+------------+------------+--------------+-----------+

ok. But PLEASE TAKE A LOOK: I added leave to Allan Max:

2019-03-19 07:00:00 |2019-03-23 15:00:00
2019-03-21 07:00:00 |2019-03-21 15:00:00

And get's only 8:00:00 of leave. Because this order ended on 22-03-2019. I'd like to get (despite of

2019-03-19 07:00:00 |2019-03-23 15:00:00

in Allan Max leave) the result:

+---------+---------+---------------+------------+------------+--------------+-----------+
|  FNAME  | LNAME   |  NUMBER_ORDER | DESC_ORDER | ORDER TIME | LEAVE_TIME   | TOTAL TIME|
+---------+---------+---------------+------------+------------+--------------+-----------+
|  ALAN   |  MARK   | TEST          | TEST       | 22:30:21   |   16:00:00   |  38:30:21 |
+---------+---------+---------------+------------+------------+--------------+-----------+
|  MARK   |  GREEN  | TEST          | TEST       | 21:19:18   |   00:00:00   |  21:19:18 |
+---------+---------+---------------+------------+------------+--------------+-----------+

2019-03-22 07:00:00 |2019-03-23 15:00:00 (to the end of order 2019-03-22) = 8:00:00 (maybe) 2019-03-21 07:00:00 |2019-03-21 15:00:00 (to the end of order 2019-03-22) = 8:00:00

So in LEAVE_TIME should be 8:00:00 + 8:00:00 = 16:00:00

It's that possible to change it? Can someone help me how to do it? thank you in advance for any help or advice.

What I have tried:

I've tried with this query:

SELECT workers.FNAME, 
workers.LNAME, 
order_statusAgg.NUMBER_ORDER,
order_statusAgg.DESC_ORDER, 
SEC_TO_TIME(SUM(order_statusAgg.stime)) AS 'ORDER TIME', 
IFNULL(SEC_TO_TIME(SUM(leaveAgg.ltime)),'00:00:00') AS 'LEAVE TIME',
ADDTIME(SEC_TO_TIME(SUM(order_statusAgg.stime)), IFNULL(SEC_TO_TIME(SUM(leaveAgg.ltime)), '00:00:00')) AS 'TOTAL TIME'
FROM workers
LEFT JOIN (SELECT leave.ID_WORKER, SUM((datediff(leave.BEGIN_DATE, leave.END_DATE) + 1) * (time_to_sec(time(leave.END_DATE)) - time_to_sec(time(leave.BEGIN_DATE)))) AS ltime FROM leave
LEFT JOIN
(SELECT ID_WORKER, MIN(BEGIN_DATE) AS 'MIN_BEGIN_DATE', MAX(END_DATE) AS 'MAX_END_DATE' 
FROM order_status GROUP BY ID_WORKER) ordstat ON 
leave.ID_WORKER = ordstat.ID_WORKER 
WHERE leave.END_DATE <= MAX_END_DATE AND leave.BEGIN_DATE >= MIN_BEGIN_DATE GROUP BY leave.ID_WORKER) leaveAgg
ON leaveAgg.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;

But unfortunately i have no clue how to change it.

解决方案

1) You've got "ALAN" working and "on leave" at the same.
2) You put "order done" info at the "worker level" (twice) when it should be at the "order level"
3) "Start and end dates" on Orders will make it easier to allocate. You're having problems now because your design is weak.

+--------+---------+---------+-------------+---------+--------------------------
|ID_LEAVE|ID_WORKER| FNAME | LNAME | BEGIN_DATE         | END_DATE             | 
+--------+---------+---------+---------+------------+--------------------+------
| 1      |   1     | ALAN  | MAX   |2019-03-22 07:00:00 |2019-03-23 15:00:00   | 
+--------+---------+---------+----------------------+---------------------------

+----------+---------+---------+---------------------+-------------------+------------+
| Id_status|ID_WORKER| ID_ORDER| BEGIN_DATE          | END_DATE          | ORDER_DONE |
+----------+---------+---------+----------+------------+---------+--------------------+
 |
| 32       |   1     |    20   |2019-03-22 06:50:20  |2019-03-22 12:22:33|  YES       |
 |
+----------+---------+---------+------------+---------+-------------------+-----------+

这篇关于sumarize如何将时间留给最大订单日期 - mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！