如何用SQL查询三张表，统计作者的书籍与歌曲数量并生成指定结果？

实现思路 #

通过两个子查询分别统计每位作者的书籍和歌曲数量（各调用一次count(*)聚合），再将这两个统计结果与Author表左连接，同时用COALESCE函数处理无书籍/歌曲作者的数值为0，确保所有作者都能出现在结果中。

完整SQL代码 #

SELECT
    a.author_id,
    a.author_name AS name,
    COALESCE(b.book_count, 0) AS `number of books`,
    COALESCE(s.song_count, 0) AS `number of songs`
FROM Author a
LEFT JOIN (
    SELECT author_id, COUNT(*) AS book_count
    FROM Books
    GROUP BY author_id
) b ON a.author_id = b.author_id
LEFT JOIN (
    SELECT author_id, COUNT(*) AS song_count
    FROM Songs
    GROUP BY author_id
) s ON a.author_id = s.author_id;

代码说明 #

• 子查询b：对Books表按author_id分组，用count(*)统计每个作者的书籍总数。
• 子查询s：对Songs表按author_id分组，用count(*)统计每个作者的歌曲总数。
• LEFT JOIN：确保即使作者没有书籍或歌曲，也能保留其记录，不会被过滤。
• COALESCE：将无对应数据时的NULL值转换为0，让统计结果更直观。

比如你提到的作者J. K. Rowling，若其author_id在Books表对应5条记录、Songs表对应8条记录，执行上述SQL后会得到符合预期的统计结果。

内容的提问来源于stack exchange，提问作者lolomeme