mysql - SQL选择所有匹配的

Question

我从 SQL-EX.RU 得到了这个 SQL 查询练习(#71)

这个练习涉及两个表:

1. 产品(制造商、型号、类型)
2. PC(代码、型号、速度、内存、高清、CD、价格)

问题是:在 PC 表中找到所有拥有其所有 PC 型号的 PC 制造商。

我写的版本是:

SELECT maker FROM 
(SELECT maker, sum(cast(model1 as int)) as sum1, sum(cast(model2 as int)) as sum2 FROM
(SELECT p.maker, p.model as model1, pc.model as model2 FROM product AS p
FULL JOIN pc ON p.model = pc.model 
WHERE p.type = 'PC') AS a
GROUP BY maker) b 
WHERE sum1 = sum2

但是结果并不完美，谁能指出我错在哪里或提供更好的解决方案？谢谢!

Answer 1

我认为下面的效果很好。它很容易理解并且可以很容易地修改以执行其他功能(“找到所有只丢失 1 的人”等)

SELECT maker
FROM (
  SELECT maker,
       SUM(CASE WHEN PC.model is null THEN 1 ELSE 0 END) AS missing_count
  FROM Product
  LEFT JOIN PC ON Product.model = PC.model
  GROUP BY maker
) x
WHERE missing_count = 0