我正在尝试从OTRS票务系统生成报告。以下是使用中的各种表格：
ticket_history
+--------+-----------------+-----------+----------+---------------------+
| id | history_type_id | ticket_id | state_id | change_time |
+--------+-----------------+-----------+----------+---------------------+
| 302214 | 1 | 12748 | 1 | 2015-09-22 21:08:10 |
| 302215 | 38 | 12748 | 1 | 2015-09-22 21:08:10 |
| 303538 | 27 | 12748 | 12 | 2015-10-07 09:44:32 |
| 303539 | 15 | 12748 | 12 | 2015-10-07 09:44:32 |
| 303540 | 20 | 12748 | 12 | 2015-10-07 09:44:32 |
| 303541 | 28 | 12748 | 12 | 2015-10-07 09:44:32 |
*| 303542 | 27 | 12748 | 3 | 2015-10-07 09:45:58 |*
| 303543 | 18 | 12748 | 3 | 2015-10-07 09:45:58 |
| 303544 | 15 | 12748 | 3 | 2015-10-07 09:45:58 |
| 303545 | 20 | 12748 | 3 | 2015-10-07 09:45:58 |
| 303546 | 17 | 12748 | 3 | 2015-10-07 09:46:32 |
| 303547 | 25 | 12748 | 3 | 2015-10-07 09:46:32 |
| 303548 | 27 | 12748 | 12 | 2015-10-07 09:46:45 |
| 303549 | 15 | 12748 | 12 | 2015-10-07 09:46:45 |
*| 303550 | 27 | 12748 | 2 | 2015-10-07 09:47:43 |*
票
+-------+------------------+----------+
| id | tn | queue_id |
+-------+------------------+----------+
| 12748 | 2015092210000093 | 5 |
+-------+------------------+----------+
时间会计
+------+-----------+-----------+---------------------+
| id | ticket_id | time_unit | change_time |
+------+-----------+-----------+---------------------+
| 3381 | 12748 | 1.00 | 2015-10-07 09:44:32 |
| 3382 | 12748 | 1.00 | 2015-10-07 09:45:58 |
| 3383 | 12748 | 0.25 | 2015-10-07 09:47:43 |
+------+-----------+-----------+---------------------+
dynamic_field_value
+--------+----------+-----------+------------+------------+-----------+
| id | field_id | object_id | value_text | value_date | value_int |
+--------+----------+-----------+------------+------------+-----------+
| 108882 | 47 | 12748 | 3 | NULL | NULL |
| 109038 | 64 | 12748 | test | NULL | NULL |
| 109040 | 98 | 12748 | -- | NULL | NULL |
| 109039 | 99 | 12748 | 0 | NULL | NULL |
我的查询：
select
ticket_history.ticket_id,
CASE WHEN dynamic_field_value.field_id = 99 THEN dynamic_field_value.value_text else NULL END AS 'V1',
CASE WHEN dynamic_field_value.field_id = 64 THEN dynamic_field_value.value_text else NULL END AS 'V2',
SUM(time_accounting.time_unit),
max(ticket_history.change_time)
from ticket_history
left join time_accounting
on ticket_history.ticket_id = time_accounting.ticket_id
left join dynamic_field_value
on ticket_history.ticket_id = dynamic_field_value.object_id
AND dynamic_field_value.field_id IN (99,64)
where ticket_history.ticket_id=12748
AND ticket_history.history_type_id IN (1,27)
AND ticket_history.state_id IN (2,3);
我得到的结果是：
+-----------+------+------+--------------------------------+---------------------------------+
| ticket_id | V1 | V2 | SUM(time_accounting.time_unit) | max(ticket_history.change_time) |
+-----------+------+------+--------------------------------+---------------------------------+
| 12748 | NULL | test | 9.00 | 2015-10-07 09:47:43 |
+-----------+------+------+--------------------------------+---------------------------------+
如您所见，我得到的总和是原始time_unit值的4倍（4 * 2.25）。我猜是因为有4次连接，这是因为有两个dynamic_field_value.value_text和where条件被匹配两次（*标记）。我该如何纠正这个问题以获得正确的总和？请注意，缩短表仅是为了节省空间，例如，实际上包含更多行。
最佳答案
您需要使用子查询进行时间计算
SELECT
ticket_history.ticket_id,
CASE WHEN dynamic_field_value.field_id = 99 THEN dynamic_field_value.value_text else NULL END AS 'V1',
CASE WHEN dynamic_field_value.field_id = 64 THEN dynamic_field_value.value_text else NULL END AS 'V2',
(SELECT SUM(time_unit) FROM time_accounting WHERE ticket_id =ticket_history.ticket_id) AS time,
MAX(ticket_history.change_time)
FROM ticket_history
LEFT JOIN time_accounting on ticket_history.ticket_id = time_accounting.ticket_id
LEFT JOIN dynamic_field_value on ticket_history.ticket_id = dynamic_field_value.object_id AND dynamic_field_value.field_id IN (99,64)
WHERE ticket_history.ticket_id=12748 AND ticket_history.history_type_id IN (1,27) AND ticket_history.state_id IN (2,3)
而我得到的输出
ticket_id V1 V2 time max(ticket_history.change_time)
12748 NULL test 2.25 2015-10-07 09:47:43