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

我正在使用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