mysql正确地构造了一个select查询 - mysql properly construct a select query

mysql properly construct a select query

我有一个包含id和值的表。它们只有在一起才是独一无二的。

ID  VALUE
1   value1
1   value2
2   value1
3   value2
3   value3
3   value4
4   value3
5   value1

假设我的值是"value2"answers"value3"，我想选择与这两个值匹配的ID（即3）

我可以

SELECT ID FROM table WHERE VALUE IN("value2", "value3")

但是，它返回的值也与任一值匹配。有没有办法只查询那些与这两个值匹配的值？

您应该将GROUP BY与HAVING 一起使用

SELECT ID FROM table WHERE VALUE IN("value2", "value3") GROUP BY ID HAVING COUNT(*) = 2

尝试以下操作：

SELECT id FROM table WHERE value = "value2" and id in (SELECT id FROM table WHERE value = "value3")

SELECT t1.id FROM table AS t1 JOIN table AS t2 ON t1.id=t2.id
  WHERE t1.value="value2" AND t2.value="value3"