MySQL同时选择This和That [英] MySQL Select both This and That

问题描述

表如下所示：

 表：商店
 
 ShopCode ShopName 
 -------- -------- 
阿拉丁
 B后街
 C书记员商店
 D Debs工具
 
表格：销售
 
 ShopCode产品
 -------- ------- 
锤子
温度计
指南针
 B橡皮擦
 B锤
 C温度计
 C锤
 D温度计
  

找到同时出售锤子和温度计的商店的名称。结果表将是

  ShopName 
 -------------- 
阿拉丁
文员商店
  

我以为以下查询可以工作，但返回的查询为空设置

  mysql> SELECT Shops.ShopName FROM商店
->加入Shops.ShopCode = Sale.ShopCode 
->上的销售Sale.Product =锤子和Sale.Product =温度计 
->按Shops.ShopCode分组； 
  

也尝试用OR代替AND，但不起作用（返回所有店铺）。可能的解决方案是什么？

为了清楚一点，我想选择同时包含商品（锤子和热电偶）的商店B出售锤子，D出售温度计，将不包括在内。

解决方案

只有两个同时出售这两个商品的A和C。 p>

您可以在销售表中加入两次，每个项目一次。如果您跳过 DISTINCT ，如果商店销售的锤子或温度计不止一个，您可能会得到重复的值。

 选择区域s.shopname 
从商店s 
联合销售s1 ON s.shopcode = s1.shopcode AND s1.product ='hammer'
联合销售s2 ON s.shopcode = s2.shopcode AND s2.product ='温度计'; 
  

...或者您可以用锤子或温度计找到所有匹配项并计算有多少个不同的值。

  SELECT s.shopname 
 FROM商店s 
联合销售s1在s.shopcode = s1.shopcode 
 s1.product IN（'hammer'，'thermometer'）
 GROUP BY s.shopname 
 HAVING COUNT（ DISTINCT s1.product）= 2; 
  

同时测试两者的SQLfiddle 。

The tables look like these:

Table: Shops

ShopCode   ShopName
--------   --------
A          Aladdin
B          Backstreet
C          Clerk's Store
D          Debs Tool

Table: Sale

ShopCode   Product
--------   -------
A          Hammer
A          Thermometer
A          Compass
B          Eraser
B          Hammer
C          Thermometer
C          Hammer
D          Thermometer

Find the name of the shops which sells BOTH Hammer and Thermometer. The result table would be

ShopName   
-------------- 
Aladdin
Clerk's Store

I thought the following query will work, but its returning empty set

mysql> SELECT Shops.ShopName FROM Shops
    -> JOIN Sale ON Shops.ShopCode=Sale.ShopCode
    -> WHERE Sale.Product='Hammer' AND Sale.Product='Thermometer'
    -> GROUP BY Shops.ShopCode;

Also tried with OR instead of AND, but not working (returning all the shops). What might be a possible solution?

Just to make it little bit clear, I want to select the shops that have both the items(hammer and thermo), even though shop B sells hammer and D sells Thermometer, they will not be included. Only A and C which is selling both the items should be on the result

解决方案

There are two fairly straight forward options.

You can join twice with the sale table, once per item. If you skip the DISTINCT, you may get duplicate values if the store sells more than one hammer or thermometer.

SELECT DISTINCT s.shopname
FROM shops s
JOIN sale s1 ON s.shopcode = s1.shopcode AND s1.product='hammer'
JOIN sale s2 ON s.shopcode = s2.shopcode AND s2.product='thermometer';

...or you can find all matches with hammer or thermometer and count how many distinct values there are. If there are two possible values and you get both, you're set.

SELECT s.shopname
FROM shops s
JOIN sale s1 ON s.shopcode = s1.shopcode
WHERE s1.product IN('hammer','thermometer')
GROUP BY s.shopname
HAVING COUNT(DISTINCT s1.product)=2;

An SQLfiddle to test both.

这篇关于MySQL同时选择This和That的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！