如何SQL查询具有其他属性的更具体变体的元素

Question

一个简短的例子：

我需要查询具有Product_ID"product1"one_answers"product2"的Store_ID。

TableName:
Storage_items

+----+-------------+----------+
| ID | Product_ID  | Store_ID |
+----+-------------+----------+
|  1 | product1    | store1   |
|  2 | product2    | store1   |
|  3 | product1    | store2   |
|  4 | product3    | store2   |
|  5 | product2    | store3   |
|  6 | product3    | store3   |
+----+-------------+----------+

Answer 1

如果您想要具有'product1'和'product2'的Store_ID，可能还有其他Product_ID：

SELECT Store_ID 
FROM Storage_items
WHERE Product_ID IN ('product1', 'product2')
GROUP BY Store_ID
HAVING COUNT(DISTINCT Product_ID) = 2

如果您想要仅具有'product1'和'product2'的Store_ID：

SELECT Store_ID 
FROM Storage_items
GROUP BY Store_ID
HAVING COUNT(DISTINCT Product_ID) = 2
AND SUM(CASE WHEN Product_ID NOT IN ('product1', 'product2') THEN 1 ELSE 0 END) = 0

Answer 2

我想，一个简单的WHERE语句就可以了。

SELECT Store_ID
FROM Storage_items
WHERE Product_ID = "product1"
OR Product_ID = "product2"

Answer 3

您可以使用一个简单的和：

SELECT Store_ID
FROM Storage_Items
WHERE Product_ID = 'product1'
AND Product_ID = 'product2'