使用“ ND”运算符的多种条件，并使用MySQL从单个表返回精确匹配的行

Question

select
games_exchange.u_exchange_id
from
games_exchange
where
( games_exchange.game_id = 7
AND games_exchange.exchange_type = 1 )
AND (
games_exchange.game_id = 7
AND games_exchange.exchange_type = 2 )
group by
games_exchange.u_exchange_id
HAVING
COUNT( games_exchange.u_exchange_id ) = 2

我从上述查询中获得的预期结果是u_exchange_id =171。但是上述查询返回了u_exchange_id = 171＆170。

Answer 1

经过大量研究，我想出了解决我问题的方法。实际上，我在使用带有条件子句的错误方式使用where条件。 下面的代码给出了针对我的问题的建议解决方案

SELECT u_exchange_id FROM games_exchange 
WHERE 
u_exchange_id IN 
( SELECT u_exchange_id FROM games_exchange WHERE game_id = 7 AND 
exchange_type = 1 )
AND 
u_exchange_id IN 
( SELECT u_exchange_id FROM games_exchange WHERE game_id = 7 AND 
exchange_type = 2)
GROUP BY u_exchange_id 
HAVING COUNT( u_exchange_id ) = 2

注意：我是通过在两个部分的游戏ID上使用foreach（）来实现的，并为每个ID动态添加了子查询。