如何正确地sumarize作为“离开时间”+“总时间” - mysql

工人表： + --------- - + --------- + --------- + | 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 JOIN #leave L ON L.id_worker = W.id_worker GROUP BY W.fname，W.lname，NUMBER_ORDER，DESC_ORDER 编辑 - 顺便说一句，我赞扬你提供表格模式，样本数据，尝试代码和预期结果，这就是我提出你的问题的原因。如果您想让我们更容易帮助您，请避免使用您的数据绘制表格，只需给我们一些简单的ddl来重现问题。我在这里解释的是我用来获得上述解决方案的示例数据：创建 table #leave（ID_LEAVE int identity （ 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 ，' 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'）选择 * from create table #workers （ID_WORKER int identity （ 1 ， 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'，' 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 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 ltimeFROM leaveGROUP 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 stimeFROM order_status INNER JOIN orders ON orders.id_order = order_status.id_orderGROUP 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_workerThen 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 the leave table - you have that information on the workers table and you have a link to that table in leave.ID_WORKER3. 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 followselect 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 totaltimefrom #workers WINNER JOIN #order_status OS ON W.id_worker = OS.id_workerINNER JOIN #orders O ON OS.id_order = O.id_orderLEFT OUTER JOIN #leave L ON L.id_worker = W.id_workerGROUP BY W.fname, W.lname, NUMBER_ORDER, DESC_ORDEREdit - 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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！