如何使用JOIN计算每位学者的论文总数（无论文者显示0）

It looks like your current query only returns pairs of authors and their individual papers, but not aggregating the count—and it's missing authors who haven't written any papers because NATURAL JOIN only includes rows where there's a match in both tables. Here's how to fix this using LEFT JOIN and aggregation functions:

SELECT a.acnum, COUNT(p.panum) AS total_papers
FROM author a
LEFT JOIN paper p ON a.acnum = p.acnum
GROUP BY a.acnum;

Let me break down what this does:

• LEFT JOIN: This ensures every author from the author table is included in the result, even if they have no matching entries in the paper table. For authors with no papers, all columns from the paper table will be NULL.
• GROUP BY a.acnum: Groups the results by each author's unique ID (acnum), so we can calculate the count per author.
• COUNT(p.panum): Counts the number of non-null panum values for each author. Since authors with no papers will have NULL for panum, this count will be 0 exactly as you need. If you used COUNT(*) instead, it would return 1 for these authors (because the LEFT JOIN still creates a row with NULLs), so targeting a column from the paper table is key here.

This query will give you a list of all acnum values paired with their total paper count, including 0 for authors with no publications.

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