技术问询：编写SQL查询年出版量超1的作者年度出书数量（用MediaCode）

Here's the SQL query that meets all your requirements:

SELECT 
    i.Author,
    i.PubYear AS Pubyear,
    COUNT(i.ItemID) AS "Total Book Published in Year"
FROM 
    Item i
INNER JOIN 
    Media m ON i.MediaID = m.MediaID
WHERE 
    m.MediaCode IS NOT NULL
GROUP BY 
    i.Author, i.PubYear
HAVING 
    COUNT(i.ItemID) > 1
ORDER BY 
    i.Author, i.PubYear;

Explanation #

Let's break down how this query works:

• Table Join: We use an INNER JOIN between the Item and Media tables to link each book to its media type. This lets us use MediaCode (as required) instead of relying solely on MediaID. The WHERE clause ensures we only include items with valid media codes.
• Grouping & Counting: We group results by Author and PubYear to calculate how many books each author published per year. COUNT(i.ItemID) counts individual book items (like physical copies). If you want to count distinct book titles instead, swap this with COUNT(DISTINCT i.Title).
• Filtering: The HAVING clause removes any author-year pairs where fewer than 2 books were published, which aligns with your requirement.
• Column Alignment: We alias PubYear to Pubyear to match your requested column name, and use a quoted alias for the count column to preserve the exact wording you specified.
• Readability: The ORDER BY clause sorts results by author and publication year for easier scanning.

Quick Notes #

• The join using MediaID is only necessary to access MediaCode—we don't use MediaID elsewhere in the query, which adheres to your constraint.
• Adjust the count function based on whether you need to count individual copies or unique book titles.

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