mysql - 具有值的返回行存在于两行中

Question

+------+------+------------+------+
| id   | type| design| location   |
+------+------+------------+------+
|    1 | abc |    A  | Location X |
|    2 | abc |    A  | Location Y |
|    3 | def |    A  | Location X |
+------+------+------------+------+

我有上面的表格。

假设我想获取位置 X 和 Y 中存在的类型。它应该返回第 1 行和第 2 行。

我应该使用什么sql？

Answer 1

SELECT type
FROM    tableName
WHERE   location IN ('location x','location y')
GROUP   BY type
HAVING  COUNT(*) = 2

• SQLFiddle Demo

或者如果您想拥有所有这些值，请使用 JOIn

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
          SELECT  type
          FROM    tableName
          WHERE   location IN ('location x','location y')
          GROUP   BY type
          HAVING  COUNT(*) = 2
        ) b ON a.type = b.type

• SQLFiddle Demo

来源

• SQL of Relational Division