MySQL COUNT()函数问题：如何查询含0合作次数的指定导演合作演员

你的原查询只返回有合作记录的演员，核心问题是WHERE子句把LEFT JOIN产生的无匹配行给过滤掉了——当你在WHERE里加了r.role_Type = "Actor"和r.movie_id IN (...)，LEFT JOIN就相当于变成了INNER JOIN，自然看不到合作次数为0的演员。

下面给你两种可行的修正方案，都能返回所有演员的合作次数（包括0次）：

方案1：调整关联逻辑，把筛选条件移到JOIN的ON子句 #

SELECT 
    p.first_name, 
    p.last_name, 
    COUNT(r_director.movie_id) as working_number 
FROM person p
-- 先关联该用户作为演员的所有角色记录（放在ON里不会过滤掉无演员角色的人）
LEFT JOIN roles r_actor 
    ON r_actor.person_id = p.id 
    AND r_actor.role_type = 'Actor'
-- 再关联同一部电影中昆汀作为导演的记录
LEFT JOIN roles r_director 
    ON r_director.movie_id = r_actor.movie_id 
    AND r_director.role_type = 'Director'
    AND r_director.person_id = (SELECT id FROM person WHERE first_name = 'Quentin' AND last_name = 'Tarantino')
GROUP BY p.id, p.first_name, p.last_name;

为什么这样有效？ #

• 把r_actor.role_type = 'Actor'放在LEFT JOIN的ON子句里，而不是WHERE，这样即使某人没有演过戏（没有演员角色记录），也会被保留下来。
• 第二次LEFT JOIN只匹配演员参演过的、由昆汀导演的电影，没有匹配的话r_director.movie_id就是NULL，COUNT(NULL)会返回0，正好对应合作次数为0的情况。

方案2：用条件聚合函数（更简洁） #

如果你觉得两次JOIN有点麻烦，也可以用SUM(CASE...)来直接统计符合条件的次数：

SELECT 
    p.first_name, 
    p.last_name, 
    SUM(
        CASE 
            -- 仅当该角色是演员，且电影是昆汀导演的，才计数1
            WHEN r.role_type = 'Actor' 
                 AND r.movie_id IN (
                     SELECT movie_id FROM roles 
                     WHERE role_type = 'Director' 
                     AND person_id = (SELECT id FROM person WHERE first_name = 'Quentin' AND last_name = 'Tarantino')
                 )
            THEN 1 
            ELSE 0 
        END
    ) as working_number 
FROM person p
LEFT JOIN roles r ON r.person_id = p.id
GROUP BY p.id, p.first_name, p.last_name;

逻辑说明： #

• 先通过LEFT JOIN关联用户的所有角色记录（不管是演员还是导演）
• 用CASE语句判断当前记录是否是“演员+昆汀导演的电影”，是就加1，否则加0
• SUM之后的结果就是该演员和昆汀的合作次数，没有合作的话就是0，同时所有演员都会被保留

小提示： #

• 记得GROUP BY要包含所有非聚合列（比如p.first_name和p.last_name），不同数据库的严格模式可能会要求这一点，避免分组错误。
• 如果你的person表有重名用户，一定要用p.id作为分组依据，确保统计的是同一个人的数据。

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