mysql - 表中查询数据的问题

Question

我有表 test 包含 4 个字段

+----+-----------+--------------+-----+
| id | int_value | string_value | qid |
+----+-----------+--------------+-----+
|  1 |       111 | Red          |   1 |
|  2 |       111 | Green        |   2 |
|  3 |       111 | Blue         |   3 |
|  4 |       222 | Yellow       |   1 |
|  5 |       222 | Red          |   2 |
|  6 |       333 | Red          |   1 |
|  7 |       333 | Green        |   2 |
+----+-----------+--------------+-----+

我想查询匹配流动约束的 int_value。

(qid = 1 and string_value = 'Red') and (qid = 2 and string_value = "Green")

结果可能是 111 和 333

如果我应用这个声明是没有意义的

select int_value from test 
where (qid = 1 and string_value = 'Red') 
and (qid = 2 and string_value = "Green")

有人可以帮助我吗？
谢谢。

Answer 1

我认为您指的不是约束，而是条件。您需要使用 OR，因为没有一行可以满足您编写的所有 WHERE 条件:

select int_value 
from test  
where (qid = 1 and string_value = 'Red')  
    or (qid = 2 and string_value = "Green") 

如果您希望 int_values 满足这两个条件，您可以这样做:

select int_value 
from test  
where (qid = 1 and string_value = 'Red')  
    or (qid = 2 and string_value = "Green")
group by int_value
having count(concat(distinct qid, string_value)) = 2