基于列值关联分组筛选：查询所有作者的已出版书籍数量

直接用左连接结合分组统计就能实现需求，完整SQL语句如下：

SELECT 
    a.id,
    a.name,
    COUNT(b.id) AS num_of_published_books
FROM 
    author a
LEFT JOIN 
    book b ON a.id = b.author_id AND b.state = 'published'
GROUP BY 
    a.id, a.name
ORDER BY 
    a.id;

关键要点说明 #

• 用LEFT JOIN保留所有作者：你之前只查到John和Jack，是因为用了内连接——内连接仅返回两张表匹配关联条件的记录，无出版书籍的作者会被过滤。LEFT JOIN会保留左表（author）的全部记录，哪怕右表（book）没有匹配项。
• 状态筛选写在ON子句中：如果把b.state = 'published'放在WHERE子句，左连接生成的NULL记录会被过滤（NULL无法满足等于'published'的条件），效果等同于内连接。必须把该条件写在JOIN的ON子句里，确保仅关联已出版书籍，同时保留无匹配的作者记录。
• COUNT(b.id)自动处理零值：左连接后，无出版书籍的作者对应的b.id为NULL，COUNT函数会自动忽略NULL值，这类作者的统计结果就是0，正好符合需求。

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