Mysql加入join子查询计数

Question

我有两个表 Loan 和 Book。

在这个查询中，我通过计算 Book_ID 来计算 Loan 中借出的图书数量:

select Book_ID, count(Book_ID) as Count
from Loan
group by Book_ID
order by Count desc;

现在我想在这里添加 Book 表中的书名。我在 Loan 表中将 Book_ID 作为外键。我不太确定如何将此查询放在一起。

这是我试过但没有结果:

select Book_ID, count(Book_ID) as Count, Book.Title
from Loan, Book
where Loan.Book_ID = Book.Book_ID
group by Book_ID
order by Count desc;

Answer 1

由于您已连接到另一个表，因此您的列名不明确，请尝试给它们起一个别名:

select Book.Book_ID, count(Book.Book_ID) as Count, Book.Title
from Loan, Book
where Loan.Book_ID = Book.Book_ID
group by Book.Book_ID
order by Count desc;