mysql - 在MySQL中形成子查询

Question

使用子查询，在哪个分支可以找到《A Guide to SQL》一书？列出分支机构名称、分支机构位置和可用副本数量。不要明确测试图书代码 669X。让 MySQL 为您完成这项工作。在此插入您的查询和结果:

下面的底部代码正确吗？我对如何在子查询中输入它感到困惑，这似乎更简单

使用 Henry_Books；

select branch_name,branch_location,inventory.on_hand
from branch,inventory,book
where branch.branch_num = inventory.branch_num
and title = "A guide to SQl" ;

Answer 1

不需要子查询:

select branch_name,branch_location,
  COUNT(inventory.book_id) NumberOfCopies
from branch
inner join inventory on branch.branch_num = inventory.branch_num 
inner join book on inventory.book_id = book.book_id
where book.title = "A guide to SQl" 
GROUP BY branch_name, branch_location;

(我不知道 book 和 inventory 这两个表之间的关系是什么，所以我猜测它将基于 book_id >)

<小时/>

如果需要使用子查询来完成，那么你可以这样做，这更复杂并且根本不需要它:

select branch_name,branch_location,
  ( SELECT COUNT(inventory.book_id) 
    FROM inventory on
    inner join book on inventory.book_id = book.book_id 
    WHERE branch.branch_num = inventory.branch_num 
     AND book.title = 'A guide to SQl'
  ) AS NumberOfCopies
from branch

请注意，这是一个相关子查询。