我正在使用OTRS服务台票务管理系统.当我执行以下Query时:
SELECT `ticket`.`id` , `ticket_history`.`ticket_id` , `ticket_history`.`id` ,
`ticket_history`.`name` , `ticket_history`.`create_time`
FROM `ticket_history`
INNER JOIN `ticket` ON `ticket_history`.`ticket_id` = `ticket`.`id`
WHERE `ticket_history`.`name` LIKE '%Raw% %new%'
OR `ticket_history`.`name` LIKE '%Close'
ORDER BY `ticket_history`.`ticket_id` , `ticket_history`.`id` ASC
我得到以下输出:
+----+-----------+-----+-------------------------------------------+---------------------+
| id | ticket_id | id | name | create_time |
+----+-----------+-----+-------------------------------------------+---------------------+
| 1 | 1 | 79 | %%Close | 2013-06-10 11:50:33 |
| 2 | 2 | 2 | %%2013060810000011%%Raw%%3 normal%%new%%2 | 2013-06-08 21:59:02 |
| 3 | 3 | 5 | %%2013060810000021%%Raw%%3 normal%%new%%3 | 2013-06-08 21:59:03 |
| 3 | 3 | 22 | %%Close | 2013-06-08 22:10:41 |
| 3 | 3 | 82 | %%Close | 2013-06-10 11:50:49 |
| 4 | 4 | 88 | %%Close | 2013-06-10 11:51:32 |
| 5 | 5 | 64 | %%2013060910000019%%Raw%%3 normal%%new%%5 | 2013-06-09 17:12:09 |
| 5 | 5 | 85 | %%Close | 2013-06-10 11:51:10 |
| 6 | 6 | 92 | %%2013061010000016%%Raw%%3 normal%%new%%6 | 2013-06-10 12:00:24 |
| 7 | 7 | 95 | %%2013061010000025%%Raw%%3 normal%%new%%7 | 2013-06-10 13:05:05 |
| 8 | 8 | 98 | %%2013061110000014%%Raw%%3 normal%%new%%8 | 2013-06-11 19:05:06 |
| 8 | 8 | 109 | %%Close | 2013-06-17 23:57:35 |
| 9 | 9 | 163 | %%2013061810000011%%Raw%%3 normal%%new%%9 | 2013-06-18 02:05:06 |
+----+-----------+-----+-------------------------------------------+---------------------+
我需要修改上面的查询,因此我只能列出每个ticket_id的行,其名称中包含“%RAW %% new%”,同时ticket_id的行中包含“%Close”.
换句话说,应该从上面的输出中显示三行ticket_id 3,两行ticket_id 5和两行ticket_id 8.
解决方法:
SELECT `ticket`.`id` , `ticket_history`.`ticket_id` , `ticket_history`.`id` ,`ticket_history`.`name` , `ticket_history`.`create_time`
FROM tickets t INNER JOIN ticket_history th
ON t.ticket_id = th.ticket_id
WHERE
EXISTS(SELECT 1 FROM ticket_history WHERE name LIKE '%Raw% %new%' AND ticket_id = t.ticket_id)
AND
EXISTS(SELECT 1 FROM ticket_history WHERE name LIKE '%Close' AND ticket_id = t.ticket_id)
标签:mysql
来源: https://codeday.me/bug/20190529/1177881.html