以下是我的代码。它返回一个人在电影中出现的每个类别的计数。它会返回结果,但我希望它列出所有类别,包括计数为0的那些。我尝试了LEFT JOIN、OUTER JOIN等所有组合,但仍然无法实现。任何帮助都将不胜感激!
SELECT c.name, COUNT(f.title) FROM category c
LEFT JOIN film_category fc ON c.category_id = fc.category_id
INNER JOIN film f ON fc.film_id = f.film_id
INNER JOIN film_actor fa ON f.film_id = fa.film_id
INNER JOIN actor a ON fa.actor_id = a.actor_id
WHERE a.first_name = 'Jack' AND a.last_name = 'Daniel'
GROUP BY c.name ASC;
...sum(if(name is null, 1, 1))...
而不是 count 可能会起作用。 - geoB