使用“与"的多重条件可用于多个条件.运算符，并使用MySQL从单个表返回完全匹配的行 [英] Multiple condition using "AND" operator and return exact matching rows from a single table using MySQL

问题描述

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.

My expected result from above query is u_exchange_id = 171. but the above query return u_exchange_id = 171 & 170.

[表的屏幕快照在这里] [2]

[the screenshot for table is here][2]

推荐答案

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

After a lot of research i came up with a solution to my problem. Actually i was using where condition in a wrong way with having clause. The proposed solution for my problem is given in below code

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动态添加了子查询.

这篇关于使用“与"的多重条件可用于多个条件.运算符，并使用MySQL从单个表返回完全匹配的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！