mysql - 多重连接和乘法

Question

我之前问了一个有关联接的问题，现在我再次陷入困境。

这是数据库的样子:

预订

Book_code (unique)
Price 
book_title
publisher_code
book_type

分支

Branch_num (unique)
Branch_name (unique)
branch_location
num_employees

库存

Branch_number
Book_code
On_hand ( the quantity of books ) 

现在的问题是:列出库存总量(即现有量乘以价格之和)大于 300 的分店。

我知道我必须利用所有的 table 。我已经开始编写以下代码:

SELECT br.branch_name, br.branch_num, i.on_hand , b.book_code, b.price
FROM book b, inventory i, branch br
WHERE br.branch_num = i.branch_num
and b.book_code = i.book_code;

但在那之后我就陷入困境了。

Answer 1

SELECT br.branch_name, br.branch_num, SUM(i.on_hand * b.price) as on_hand_price
FROM book b, inventory i, branch br
WHERE br.branch_num = i.branch_num
and b.book_code = i.book_code
GROUP by br.branch_num,br.branch_name
Having Sum(i.on_hand* b.price) > 300
ORDER BY branch_name;

您刚刚错过了 GROUP遍历分支编号和名称..并最终验证 SUM使用hAVING检查该组中的现有金额.