如何正确地sumarize作为“离开时间”+“总时间” - mysql [英] How to sumarize correctly as “leave time” + “total time” - mysql
本文介绍了如何正确地sumarize作为“离开时间”+“总时间” - mysql的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
在mysql数据库中我创建了离开表：
+ -------- + --- ------ + --------- + ------------- + --------- --------- + ---------------------- +
| ID_LEAVE | ID_WORKER | FNAME | LNAME | BEGIN_DATE | END_DATE |
+ -------- + --------- + --------- + --------- + ------ ------ + -------------------- + -----------
| 1 | 1 |大卫| BUCS | 2019-03-19 07:00:00 | 2019-03-19 15:00:00 |
+ -------- + --------- + --------- + ---------------- ------ + -------------------- + -----------
| 2 | 2 | MARK | GREEN | 2019-03-21 07:00:00 | 2019-03-21 15:00:00 |
+ -------- + --------- + --------- + ---------------- ------ + -------------------------------- +
工人表：
+ --------- - + --------- + --------- +
| ID_WORKER | FNAME | LNAME |
+ ---------- + --------- + ----------
| 1 |大卫| BUCS |
+ ---------- + --------- + ----------
| 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 table）在订单上包括来自Leave表的sumarizing离开时间。我已经正确选择了工人（LNAME，FNAME）订单（DESC_ORDER和NUMBER_ORDER）和TOTAL TIME。我在下面编写了mysql命令：
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'，
SEC_TO_TIME（SUM（leaveAgg.ltime））AS 'LEAVE TIME'
来自工人
INNER JOIN（
SELECT leave.id_worker，SUM（（Time_to_sec（leave.end_date） -
Time_to_sec（leave.begin_date）））
FROM离开
GROUP BY leave.id_worker
）leaveAgg
ON leaveAgg.id_worker = workers.id_worker
INNER 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订单.id_order = order_status.i d_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
然后在那个命令后我得到：
+ --------- + --------- + --------------- + -------- ---- + ------------ + -------------- +
| FNAME | LNAME | NUMBER_ORDER | DESC_ORDER |总时间| LEAVE_TIME |
+ --------- + --------- + --------------- + --------- --- + ------------ + -------------- +
|大卫| BUCS |测试|测试| 22:30:21 | 8:00:00 |
+ --------- + --------- + --------------- + --------- --- + ------------ + -------------- +
| MARK |绿色|测试|测试| 21:19:18 | 8:00:00 |
+ --------- + --------- + --------------- + --------- --- + ------------ + -------------- +
我接下来想做什么：
我接下来想做什么：我正在尝试sumarize所有他们，我的意思是：每个工人的总时间+ LEAVE_TIME。例如：
+ --------- + --------- + ---- ----------- ------------ + ------------ + ------------ + - + ---------- +
| FNAME | LNAME | NUMBER_ORDER | DESC_ORDER |总时间| LEAVE_TIME | SUM_TIME |
+ --------- + --------- + --------------- + --------- --- + ------------ + -------------- + ---------- +
|大卫| BUCS |测试|测试| 22:30:21 | 8:00:00 | 30:30:21 |
+ --------- + --------- + --------------- + --------- --- + ------------ + -------------- + ---------- +
| MARK |绿色|测试|测试| 21:19:18 | 8:00:00 | 29:19:18 |
+ --------- + --------- + --------------- + --------- --- + ------------ + -------------- + ---------- +
我不知道如何解决它。有人知道如何解决它？有任何想法吗。非常感谢任何帮助！
我尝试过：
我试过添加到顶部查询：
SEC_TO_TIME（SUM（SEC_TO_TIME（SUM（order_statusAgg.ttime）+ SUM（leaveAgg） .stime））AS'SUM_TIME';
但我知道这是一个坏主意。
解决方案
1.使用SEC_TO_TIME，TIME_TO_SEC似乎有点矫枉过正。为什么不使用 TIMESTAMPDIFF [ ^ ]
2.您的数据库设计不佳 - 无需重复工作人员姓名中的姓氏和表 - 您在workers表中有这些信息，并且您有一个指向该表的链接leave.ID_WORKER
3.我看不出所有这些子查询的任何原因（并且它不会为我编译）以下内容更简单，更容易遵循
选择 W.fname，W.lname，NUMBER_ORDER，DESC_ORDER
，SUM（TIMESTAMPDIFF（HOUR，os。 begin_date，os.end_date）） as hoursWorkedOnOrders
，SUM（TIMESTAMPDIFF（HOUR，l.BEGIN_DATE，L.END_DATE）） as leavetime
，SUM（TIMESTAMPDIFF（HOUR，os.begin_date，os.end_date））+ SUM（TIMESTAMPDIFF（HOUR，l.BEGIN_DATE，L.END_DATE）） as totaltime
来自 #workers W
INNER JOIN #order_status OS ON W.id_worker = OS.id_worker
INNER JOIN #orders O ON OS.id_order = O.id_order
LEFT OUTER < span class =code-keyword> JOIN #leave L ON L.id_worker = W.id_worker
GROUP BY W.fname，W.lname，NUMBER_ORDER，DESC_ORDER
编辑 - 顺便说一句，我赞扬你提供表格模式，样本数据，尝试代码和预期结果，这就是我提出你的问题的原因。如果您想让我们更容易帮助您，请避免使用您的数据绘制表格，只需给我们一些简单的ddl来重现问题。我在这里解释的是我用来获得上述解决方案的示例数据：
创建 < span class =code-keyword> table #leave（ID_LEAVE int identity （< span class =code-digit> 1 ， 1 ），ID_WORKER int ，FNAME varchar （ 30 ），LNAME varchar （ 30 ），BEGIN_DATE datetime ，END_DATE datetime ）
插入 进入 #leave（ID_WORKER，FNAME，LNAME，BEGIN_DATE，END_DATE） values
（ 1 ，' < span class =code-string> DAVID'，' BUCS'，' 2019-03-19 07:00:00'，' 2019-03-19 15:00： 00'），
（ 2 ，' MARK'，' GREEN'，' 2019-03-21 07:00:00'，' < span class =code-string> 2019-03-21 15:00:00'）
选择 * from
create table #workers （ID_WORKER int identity （ 1 ，< span class =code-digit> 1 ），FNAME varchar （ 30 ），LNAME varchar （ 30 ））
插入 进入 #workers（FNAME，LNAME）值
（' DAVID'，' BUCS'），
（' MARK'，' GREEN'）
创建 表 #orders（ID_ORDER int ，DESC_ORDER varchar （ 30 ），NUMBER_ORDER varchar （ 30 ））
insert into #orders（ID_ORDER，DESC_ORDER，NUMBER_ORDER）值
（ 20 ，' TEST'，' TEST'）
create table #order_status（ Id_status int identity （ 30 ， 1 ），ID_WORKER int ，ID_ORDER int ，BEGIN_DATE datetime ，END_DATE datetime ，ORDER_DONE varchar （ 3 ））
insert into #order_status（ID_WORKER，ID_ORDER，BEGIN_DATE，END_DATE，ORDER_DONE） 值
（ 1 ， 20 ，' 2019-03-18 06:50:35'，' 2019-03-18 15:21:32'，' < span class =code-string> NO'），
（ 1 ， 20 ，' 2019-03-20 06:44:12'，' 2019-03-20 15:11:23'，' NO'），
（ 1 ， 20 ，' 2019-03-22 06:50:20'，' 2019-03-22 12:22:33'，' YES'），
（ 2 ， 20 ，' 2019-03-18 06:45 ：11'，' 2019-03-18 15:14:45'，< span class =code-string>' NO'），
（ 2 ， 20 ，' 2019-03-20 06 ：50：22'，' 2019-03-20 15:10:32' ，' NO'），
（ 2 ， 20 ，' 2019-03- 22 06:54:11'，' 2019-03-22 1 1:23:45'，' YES'）
In mysql database i've created "leave" table:
+--------+---------+---------+-------------+---------+-------------------------------+ |ID_LEAVE|ID_WORKER| FNAME | LNAME | BEGIN_DATE | END_DATE | +--------+---------+---------+---------+------------+--------------------+----------- | 1 | 1 | DAVID | BUCS |2019-03-19 07:00:00 |2019-03-19 15:00:00 | +--------+---------+---------+----------------------+--------------------+----------- | 2 | 2 | MARK | GREEN |2019-03-21 07:00:00 |2019-03-21 15:00:00 | +--------+---------+---------+----------------------+--------------------------------+
"Workers" table:
+----------+---------+---------+ |ID_WORKER | FNAME | LNAME | +----------+---------+---------- | 1 | DAVID | BUCS | +----------+---------+---------- | 2 | MARK | GREEN | +----------+---------+---------+
"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. 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',
SEC_TO_TIME(SUM(leaveAgg.ltime)) AS 'LEAVE TIME'
FROM workers
INNER JOIN (
SELECT leave.id_worker, SUM((Time_to_sec(leave.end_date) -
Time_to_sec(leave.begin_date))) AS ltime
FROM leave
GROUP BY leave.id_worker
) leaveAgg
ON leaveAgg.id_worker = workers.id_worker
INNER 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 after that command i get:
+---------+---------+---------------+------------+------------+--------------+ | FNAME | LNAME | NUMBER_ORDER | DESC_ORDER | TOTAL TIME | LEAVE_TIME | +---------+---------+---------------+------------+------------+--------------+ | DAVID | BUCS | TEST | TEST | 22:30:21 | 8:00:00 | +---------+---------+---------------+------------+------------+--------------+ | MARK | GREEN | TEST | TEST | 21:19:18 | 8:00:00 | +---------+---------+---------------+------------+------------+--------------+
what i'd like to do next:
What i'd like to do next: I'm trying sumarize all of them, i mean: TOTAL TIME + LEAVE_TIME for each Worker. For example:
+---------+---------+---------------+------------+------------+-------------+----------+ | FNAME | LNAME | NUMBER_ORDER | DESC_ORDER | TOTAL TIME | LEAVE_TIME | SUM_TIME | +---------+---------+---------------+------------+------------+--------------+----------+ | DAVID | BUCS | TEST | TEST | 22:30:21 | 8:00:00 | 30:30:21 | +---------+---------+---------------+------------+------------+--------------+----------+ | MARK | GREEN | TEST | TEST | 21:19:18 | 8:00:00 | 29:19:18 | +---------+---------+---------------+------------+------------+--------------+----------+
I have no clue how to solve it. Has someone idea how to solve it? Any ideas. Thx very much for any help!
What I have tried:
i've tried add to at the top query:
SEC_TO_TIME(SUM(SEC_TO_TIME(SUM(order_statusAgg.ttime) + SUM(leaveAgg.stime)) AS 'SUM_TIME';
But i know that's a bad idea.
解决方案
1. Using SEC_TO_TIME, TIME_TO_SEC seems like overkill. Why not just use TIMESTAMPDIFF[^]
2. You have a poor db design - there is no need to repeat the workers forename and surname in theleavetable - you have that information on theworkerstable and you have a link to that table inleave.ID_WORKER
3. I can't see any reason for all of those sub-queries (and it wouldn't compile for me) Something like the following is far simpler, and easier to follow
select W.fname, W.lname, NUMBER_ORDER, DESC_ORDER ,SUM(TIMESTAMPDIFF(HOUR, os.begin_date, os.end_date)) as hoursWorkedOnOrders ,SUM(TIMESTAMPDIFF(HOUR, l.BEGIN_DATE, L.END_DATE)) as leavetime ,SUM(TIMESTAMPDIFF(HOUR, os.begin_date, os.end_date)) + SUM(TIMESTAMPDIFF(HOUR, l.BEGIN_DATE, L.END_DATE)) as totaltime from #workers W INNER JOIN #order_status OS ON W.id_worker = OS.id_worker INNER JOIN #orders O ON OS.id_order = O.id_order LEFT OUTER JOIN #leave L ON L.id_worker = W.id_worker GROUP BY W.fname, W.lname, NUMBER_ORDER, DESC_ORDER
Edit - as an aside, I commend you for providing your table schemas, sample data, attempted code and expected results which is why I am upvoting your question. If you want to make it even easier for us to help you, avoid drawing tables with your data and just give us some simple ddl to reproduce the problem. To explain what I mean here is the sample data I used to get the above solution:
create table #leave(ID_LEAVE int identity(1,1),ID_WORKER int, FNAME varchar(30), LNAME varchar(30), BEGIN_DATE datetime, END_DATE datetime) insert into #leave(ID_WORKER, FNAME, LNAME, BEGIN_DATE, END_DATE) values (1, 'DAVID', 'BUCS','2019-03-19 07:00:00','2019-03-19 15:00:00'), (2, 'MARK', 'GREEN','2019-03-21 07:00:00','2019-03-21 15:00:00') select * from create table #workers(ID_WORKER int identity(1,1), FNAME varchar(30) , LNAME varchar(30)) insert into #workers (FNAME , LNAME) values ('DAVID', 'BUCS'), ('MARK', 'GREEN') create table #orders (ID_ORDER int, DESC_ORDER varchar(30), NUMBER_ORDER varchar(30)) insert into #orders (ID_ORDER , DESC_ORDER , NUMBER_ORDER) values (20, 'TEST', 'TEST') create table #order_status(Id_status int identity(30,1),ID_WORKER int, ID_ORDER int, BEGIN_DATE datetime, END_DATE datetime, ORDER_DONE varchar(3)) insert into #order_status (ID_WORKER, ID_ORDER, BEGIN_DATE, END_DATE, ORDER_DONE) values (1, 20 ,'2019-03-18 06:50:35' ,'2019-03-18 15:21:32', 'NO' ) , (1, 20 ,'2019-03-20 06:44:12' ,'2019-03-20 15:11:23', 'NO' ) , (1, 20 ,'2019-03-22 06:50:20' ,'2019-03-22 12:22:33', 'YES' ) , (2, 20 ,'2019-03-18 06:45:11' ,'2019-03-18 15:14:45', 'NO' ) , (2, 20 ,'2019-03-20 06:50:22' ,'2019-03-20 15:10:32', 'NO' ) , (2, 20 ,'2019-03-22 06:54:11' ,'2019-03-22 11:23:45', 'YES' )
这篇关于如何正确地sumarize作为“离开时间”+“总时间” - mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
