MySQL具有相同ID的多行同时满足两个不同的条件

Question

我正在使用 OTRS 服务台工单管理系统。当我执行以下查询时:

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 |+----+-----------+-----+-------------------------------------------+---------------------+

我需要修改上述查询，以便我只能列出名称中包含“%RAW% %new%”的每个 ticket_id 的行，同时同一 ticket_id 中有一行包含“%Close”。

换句话说，上面的输出应该显示三行 ticket_id 3、两行 ticket_id 5 和两行 ticket_id 8。

Answer 1

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)