MySQL - 获取左表元素，尽管 'where' 条件不兼容

Question

我有两张 table ，与员工和他/她的书相匹配。我想让所有员工都得到结果，尽管他/她没有书(计数为 0)。但是，我在下面编写的代码仅返回至少拥有一本与 where 条件兼容的书籍的员工。如何让所有员工和没有书的员工计数为0？

SELECT staff.name AS sname, Count(book.book_id) AS bcount 
FROM db_staff AS staff 
LEFT JOIN db_book AS book ON book.staff_id = staff.staff_id 
WHERE (book.status_id = 1 OR book.status_id = 3) 
GROUP BY staff.staff_id

Answer 1

将过滤条件从 Where 移至 And 子句，如下所示:

SELECT staff.name AS sname, Count(book.book_id) AS bcount 
FROM db_staff AS staff 
LEFT JOIN db_book AS book ON book.staff_id = staff.staff_id 
AND (book.status_id = 1 OR book.status_id = 3) 
GROUP BY staff.staff_id

说明:当有where子句时，会对join结果进行过滤，不满足条件的行会被过滤掉。当相同的条件成为 on 子句的一部分时，它将被视为 join 条件之一。因此，在左联接中，即使右侧没有满足联接中所有条件的行，也会返回左侧行。

http://sqlfiddle.com/#!2/75344/3