MySQL - 访问表总和并与另一个表进行比较？

Question

这是一项家庭作业。我只是不明白该怎么做。

此特定问题的说明是:

List the branch name for all branches that have at least one
book that has at least 4 copies on hand.

有问题的表在哪里:

分支:

BranchName        | BranchId
Henry Downtown    |    1
16 Riverview      |    2
Henry On The Hill |    3

库存:

BookId   |  BranchId  |  OnHand
   1     |     1      |     2
   2     |     3      |     4
   3     |     1      |     8
   4     |     3      |     1
   5     |     1      |     2
   6     |     2      |     3

据我了解，我可以通过以下方式获取每个分支名称的 OnHand 数量:

SELECT BranchName, SUM(OnHand) 
FROM Branch B, Inventory I 
WHERE B.BranchId = I.BranchId 
GROUP BY BranchName;

但我不明白如何比较每个分支的 OnHand 总和与 4。

伙计们，任何帮助将不胜感激!

Answer 1

类似这样的东西(假设同一本书可以在不同的分支中):

SELECT DISTINCT BranchName 
FROM Branch B, Inventory I 
WHERE B.BranchId = I.BranchId 
GROUP BY BranchName, I.BookId
HAVING SUM(onHand) >= 4;

SQL Fiddle

你就是这么想的吗？

编辑

如果 Inventory 表每个分支仅列出一次每本书，那么我们可以摆脱按 bookid 和 DISTINCT 子句进行分组。

查询变为:

SELECT BranchName 
FROM Branch B, Inventory I 
WHERE B.BranchId = I.BranchId 
GROUP BY BranchName
HAVING SUM(onHand) >= 4;

This SQL Fiddle用于简化查询。