基于Sakila数据库查询演员参演所有类别的次数（含0次）

Hey there! The issue with your current query is that you're using inner joins (JOIN without LEFT), which only return rows where there's a matching record across all joined tables. That's why categories the actor hasn't worked in are getting filtered out entirely.

To fix this, we need to start from the category table and use left joins to connect to the other tables, ensuring every category is retained even if there's no matching film or actor record. Here's the adjusted query:

SELECT 
    c.name AS genre,
    COUNT(fa.film_id) AS appear
FROM 
    category c
LEFT JOIN 
    film_category fc ON c.category_id = fc.category_id
LEFT JOIN 
    film_actor fa ON fc.film_id = fa.film_id AND fa.actor_id = 1
GROUP BY 
    c.category_id, c.name
ORDER BY 
    appear ASC;

Key Changes Explained: #

• Start with category + Left Joins: By starting from the category table and using LEFT JOIN for all subsequent connections, we guarantee every category is included in the result set, even if there's no matching film or actor data.
• Filter Actor ID in the Join Condition: Adding fa.actor_id = 1 directly to the film_actor join condition (instead of a WHERE clause) ensures we only pull records for that specific actor without filtering out categories with no matches.
• Count the Right Field: Using COUNT(fa.film_id) instead of COUNT(c.name) works because COUNT() ignores NULL values. For categories the actor hasn't worked in, fa.film_id will be NULL, so the count returns 0. If you used COUNT(c.name), it would always return at least 1 (since c.name is never NULL), which isn't what we want.
• Group by Category ID + Name: Grouping by both category_id (the unique identifier) and name ensures we don't get duplicate rows if two categories ever had the same name (unlikely in Sakila, but it's a best practice).

This query will now return every category in the Sakila database, with the actor's appearance count (0 if they haven't worked in that category) sorted from lowest to highest.

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