在mysql数据库中，我创建了“疾病”表：
+--------+---------+---------+-------------+---------+-------------------------------+
| Id_SICK|ID_WORKER| FNAME | LNAME | BEGIN_DATE | END_DATE |
+--------+---------+---------+---------+------------+--------------------+-----------+
| 4 | 26 | ANDREW | WORM |2019-03-19 07:00:00 |2019-03-19 15:00:00 |
+--------+---------+---------+----------------------+--------------------+-----------+
| 5 | 25 | ADAM | GAX |2019-03-21 07:00:00 |2019-03-21 15:00:00 |
+--------+---------+---------+----------------------+--------------------------------+
“工人”表：
+--------+---------+---------+--
|ID_WORKER | FNAME | LNAME |
+----------+---------+----------
| 25 | ADAM | GAX |
+----------+---------+----------
| 26 | ANDREW | WORM |
+----------+---------+----------
“订单”表：
+--------+---------+---------+------------+
|ID_ORDER | DESC_ORDER | NUMBER_ORDER |
+----------+---------+--------------------+
| 20 | TEST | TEST |
+----------+---------+--------------------+
和“ Order_status”表：
+--------+---------+---------+---------+-------------+--------+----------+------------+
| Id_status|ID_WORKER| ID_ORDER| BEGIN_DATE | END_DATE | ORDER_DONE |
+----------+---------+---------+----------+------------+---------+--------------------+
| 47 | 25 | 20 |2019-03-18 06:50:35 |2019-03-18 15:21:32| NO |
+----------+---------+---------+------------+---------+-------------------+-----------+
| 48 | 25 | 20 |2019-03-20 06:44:12 |2019-03-20 15:11:23| NO |
+----------+---------+---------+------------+---------+-------------------+-----------+
| 50 | 25 | 20 |2019-03-22 06:50:20 |2019-03-22 12:22:33| YES |
+----------+---------+---------+------------+---------+-------------------+-----------+
| 51 | 26 | 20 |2019-03-18 06:45:11 |2019-03-18 15:14:45| NO |
+----------+---------+---------+------------+---------+-------------------+-----------+
| 52 | 26 | 20 |2019-03-20 06:50:22 |2019-03-20 15:10:32| NO |
+----------+---------+---------+------------+---------+-------------------+-----------+
| 53 | 25 | 20 |2019-03-22 06:54:11 |2019-03-22 11:23:45| YES |
+----------+---------+---------+------------+---------+-------------------+-----------+
我想在订单上总结每个工人的“总计时间”（在order_status表中），包括总结来自疾病表的“疾病时间”。我已经正确选择了彼此的工作人员订单（LNAME，FNAME）订单（DESC_ORDER和NUMBER_ORDER）和“ TOTAL TIME”。但是我无法总结疾病时间。我在下面写了mysql命令：
SELECT workers.FNAME, workers.LNAME, orders.NUMBER_ORDER, orders.DESC_ORDER, SEC_TO_TIME(SUM(TIME_TO_SEC(order_status.END_DATE) - TIME_TO_SEC(order_status.BEGIN_DATE))) AS 'TOTAL TIME', SEC_TO_TIME(SUM(TIME_TO_SEC(sickness.END_DATE) - TIME_TO_SEC(sickness.BEGIN_DATE))) AS 'SICKNESS TIME' FROM order_status INNER JOIN workers ON workers.ID_WORKER = order_status.ID_WORKER INNER JOIN orders ON orders.ID_ORDER = order_status.ID_ORDER INNER JOIN sickness ON sickness.ID_WORKER = workers.ID_WORKER WHERE orders.NUMBER_ORDER LIKE 'TEST' GROUP BY workers.ID_WORKER
然后我得到了那个结果：
+--------+---------+---------+-------+------------+------------+-------------+
| FNAME | LNAME | NUMBER_ORDER | DESC_ORDER | TOTAL TIME | SICKNESS_TIME|
+----------+---------+---------------+------------+------------+-------------+
| ADAM | GAX | TEST | TEST | 22:25:38 | 24:00:00 |
+----------+---------+---------------+------------+------------+-------------+
| ANDREW | WORM | TEST | TEST | 22:52:12 | 24:00:00 |
+----------+---------+---------------+------------+------------+-------------+
至于“疾病时间不正确”，因为从ID分组后的“疾病”是
+--------+---------+-----+
| Id_SICK| SICKNESS TIME |
+--------+---------+-----+
| 4 | 08:00:00 |
+--------+---------+-----+
| 5 | 08:00:00 |
+--------+---------+-----+
例如，我也总结了“ TOTAL TIME + SICKNESS TIME”
TOTAL TIME: 22:25:38
SICKNESS TIME: 8:00:00
TOTAL + SICKNESS TIME : 22;25:38 + 8:00:00 = 30:25:38
有人可以帮我怎么处理吗？我应该写什么样的mysql查询？有任何想法吗？感谢任何帮助:)
最佳答案
这实际上是预期的。上面的查询正在联接4个表：
订单状态
疾病
工人
订单
“工人”表与“ order_status”具有1：N的关系。
同样，“工人”与疾病有1：N的关系，即使这是1：1，实际上也没有关系。
该查询正在上述表之间创建笛卡尔乘积，并且每个表中的重复列值可以位于上面查询的结果集中。
删除group by和sum以查看结果集。
例如，在id_worker 25的示例中，您有3个order_status行与1个疾病行连接（此行值将重复3次），与1个工人行（同样将重复3次），以及1个订单行（相同）。
因此，聚合函数sum合并重复值。
仅当结果集包含用于聚合函数的所有列的唯一行时，这才起作用
要解决此问题，请使用子查询来汇总结果：
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(sicknessAgg.stime)) AS 'SICKNESS TIME'
FROM workers
INNER JOIN (
SELECT sickness.id_worker, SUM((Time_to_sec(sickness.end_date) -
Time_to_sec(sickness.begin_date))) AS stime
FROM sickness
GROUP BY sickness.id_worker
) sicknessAgg
ON sicknessAgg.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
请注意，在这种情况下，需要从Workers表开始，因为您要从
order_status和sickness表中聚合不同数量的行。参考文献：
How to join three tables to get Sum
MySQL JOIN with multiple tables and SUMS
How get the sum of two tables value using inner join