表中查询数据的问题 [英] Problem with Query Data in a Table

问题描述

我有表 test 包含 4 个字段

I have table test contain 4 fields

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

I want to query int_value that match flowing constraints.

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

结果可能是 111 和 333

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

It's not make sense if I apply this statement

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

有人可以帮我吗?
谢谢.

Does one can help me?
Thank you.

推荐答案

你可以对 COUNT 使用一个技巧:

You could use a trick with COUNT:

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(DISTINCT qid, string_value) = 2

不过，这完全未经测试，所以我不确定语法是否正确.基本上，它按 int_value 对结果进行分组，并查找结果与不同情况一样多的任何组.您需要让 HAVING 子句中的数值与 OR 替代项的数量相匹配.

This is completely untested, though, so I'm not sure if even the syntax is correct. Basically it groups the results by the int_value, and finds any group where there's as many results as there are different cases. You need to have the numeric value in the HAVING clause match the number of OR alternatives.

这篇关于表中查询数据的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！