Mysql:SUM() 给出 4 倍的值与多个连接和 where 条件

Question

我正在尝试从 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 |
+-------+------------------+----------+

time_accounting

+------+-----------+-----------+---------------------+
| 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*2.25) 的 4 倍。我猜这是因为 4 次连接，这是因为两个 dynamic_field_value.value_text 和 where 条件匹配两次(* 标记)。我怎样才能更正这个问题以获得正确的金额？请注意，为了节省空间，表格已缩短，但实际上包含更多行。

Answer 1

你需要使用子查询来计算时间

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