如何从数据库中选择所有作者并分配给他们的书籍数量?

Question

我有以下数据库结构：

Authors(id,name);
Books(id,title,authorId);

我想从作者那里选择所有字段以及他们分配到的书籍数量。我已经设法得到了结果，但仅适用于至少分配给一本书的作者，这不是我想要的。我尝试使用以下查询：

SELECT books.*,authors.* 
FROM authors 
FULL OUTER JOIN books 
ON authors.id = books.authorId;

但它不起作用。

Answer 1

我猜你想要一个left join和聚合：

select a.id, a.name, count(*)
from authors a
left join books b on b.authorId = a.id
group by a.id, a.name

Answer 2

outer join将带回没有书籍的作者。改用inner join，您的结果只会带回至少 1 本书的作者。

Answer 3

我会推荐一个相关的子查询：

SELECT a.*,
(SELECT COUNT(*)
FROM books b
WHERE a.id = b.authorId
) as num_books
FROM authors;

这允许您使用来自authorsSELECT a.*。 如果在外部查询中放置GROUP BY，则需要单独列出所有列，或者使用允许您按主键聚合的数据库，同时选择其他列(这是标准功能，但大多数数据库不支持它(。

Answer 4

当然，您需要 LEFT JOIN 和 GROUP BY，但从任务描述中细节不够清楚。让我们尝试一种

SELECT b.*, ab.count
FROM authors AS a 
LEFT JOIN (
SELECT authorId, COUNT(*) AS count
FROM books 
GROUP BY authorId
) AS ab ON a.id = ab.authorId;

另外，如果您不想为某些作者获取 NULL，则可以应用以下表达式：

IFNULL(ab.count, 0) AS count