基于Customer、Item、Order表的多表SQL数据检索实现咨询

所有查询都基于三个表的固定关联关系搭建：

• 客户表Customer和订单表Order通过客户唯一标识cid关联
• 订单表Order和商品表Item通过商品唯一标识iid关联
• 注意：Order是SQL语法保留字，写SQL引用该表名时必须用反引号包裹，否则会触发语法报错

需求1：获取订购过iid为100的商品的客户姓名 #

不需要关联商品表，直接从订单表匹配对应商品的订单，关联客户表取姓名，加去重逻辑避免同一客户多次下单被重复统计。

SELECT DISTINCT c.cname
FROM Customer c
INNER JOIN `Order` o 
  ON c.cid = o.cid
WHERE o.iid = 100;

需求2：获取订购过类型为“laptop”的商品的客户姓名 #

需要额外关联商品表，因为商品存储类型字段在Item表中，同样加去重逻辑避免重复返回同一客户。

SELECT DISTINCT c.cname
FROM Customer c
INNER JOIN `Order` o 
  ON c.cid = o.cid
INNER JOIN Item i 
  ON o.iid = i.iid
WHERE i.type = 'laptop';

需求3：两类目标客户的姓名提取 #

3.1 同时订购过laptop、desktop类型商品的客户 #

用分组聚合的方式实现，先过滤出和两类目标商品相关的订单，按客户分组后判断组内覆盖的商品类型数是否等于2，就能精准匹配同时买过两类商品的客户，不会出现买了一类就被误判的情况。

SELECT c.cname
FROM Customer c
INNER JOIN `Order` o 
  ON c.cid = o.cid
INNER JOIN Item i 
  ON o.iid = i.iid
WHERE i.type IN ('laptop', 'desktop')
GROUP BY c.cid, c.cname -- 用cid分组避免同名客户统计混淆
HAVING COUNT(DISTINCT i.type) = 2;

3.2 订购过所有商品的客户 #

属于典型的关系除法场景，核心判断逻辑是：客户累计购买过的不重复商品数量，等于数据库内全部商品的总数量，就说明该客户买过所有商品。

SELECT c.cname
FROM Customer c
INNER JOIN `Order` o 
  ON c.cid = o.cid
INNER JOIN Item i 
  ON o.iid = i.iid
GROUP BY c.cid, c.cname
HAVING COUNT(DISTINCT i.iid) = (SELECT COUNT(*) FROM Item);

内容的提问来源于stack exchange，提问作者Goru Chagol