我必须找到只销售1种特定产品的商店
我必须加入3张表才能获得所有信息这就是连接表的
Store | Product |
----------------
A | Juice | Mon
A | Milk | Mon
A | Milk | Tue
B | Juice | Mon
B | Beer | Tue
B | Milk | Wed
B | Beer | Thu
C | Beer | Mon
D | Beer | Thu
D | Beer | Fri
我想让只卖啤酒的商店,在这种情况下,啤酒显然是C和D。如何编写可以筛选出这些存储的查询?
您可以使用聚合:
select store
from t
group by store
having min(product) = max(product);
首先根据商店分组,然后检查商店销售的产品数量。
SELECT Store, Count(Product) AS pcount
FROM joined_table
GROUP BY Store
HAVING pcount=1;
您可以在HAVING
子句中设置条件:
SELECT store
FROM (<your query here>) t
GROUP BY store
HAVING SUM(product <> 'beer') = 0;
条件SUM(product <> 'beer') = 0
过滤掉任何销售与'beer'
不同的商品的商店。