左联接未返回左表中的所有行

Question

SELECT category.name FROM category 

Action
Animation
Children
Classics
Comedy
Documentary
Drama
Family
Foreign
Games
Horror
Music
New
Sci-Fi
Sports
Travel

SELECT q1.name AS 'Film Category', COUNT(q2.name) AS '# of Films Ed Chase has appeared in' 
FROM (
    SELECT category.name FROM category 
) AS q1
LEFT JOIN (
    SELECT category.name FROM category 
    INNER JOIN film_category ON film_category.category_id = category.category_id
    INNER JOIN film ON film.film_id = film_category.film_id
    INNER JOIN film_actor ON film.film_id = film_actor.film_id
    INNER JOIN actor ON actor.actor_id = film_actor.actor_id
    WHERE actor.first_name = 'ED' AND actor.last_name = 'CHASE'
) AS q2
ON q1.name = q2.name AND '# of Films Ed Chase has starred in' >= 0
GROUP BY q2.name
ORDER BY q2.name;

它还会返回这个表，遗漏一些行（我希望它给出所有流派，而不仅仅是演员在其中扮演角色的流派）：

Film Category   # of Films Ed Chase has appeared in
Animation       0
Action          2
Classics        2
Documentary     6
Drama           3
Foreign         2
Music           1
New             2
Sci-Fi          1
Sports          2
Travel          1

有趣的是，它为“动画”返回了“0”，但为“儿童”或“喜剧”没有行，这是我正在寻找的结果（所有流派都返回了）。我做错了什么？

Answer 1

count()不对null进行计数，您的左侧联接将null放在右侧表中没有值的记录中。您的外部join实际上是（或者应该是--检查这个）返回所有需要的行，但是带有聚合函数的外部select正在吃掉它们。

尝试将count()移到右侧子查询中，然后在外部查询中用ifNull(q2.count,“0”）替换它，以将Null替换为0。

我将注意到，您应该在Q1.name上分组，而不是Q2.name上分组。