列出所有表元素和count()，即使count为0也是如此

Question

假设下表：

table book(
  id,
  title,
  deleted
)
table invoice(
  id,
  book_id,
  settled
)

我需要一份所有书的清单，以及每本书的结算发票数量。

我试过这个：

select book.id, title, count(invoice.id)
from book LEFT OUTER JOIN invoice ON book.id=invoice.book_id
where deleted=0
    and settled=1
group by book.id

只有当一本书至少有一张已结算的发票，或者根本没有任何发票时，这才有效。然而，当账簿有未结算的发票，并且没有任何已结算的发票时，它就会失败。

你知道怎么查询它吗？

Answer 1

下面将列出所有帐簿，但仅连接和计算已结算的发票。

SELECT 
    b.id, b.title, COUNT(i.id) AS settled
FROM 
    book b
LEFT JOIN invoice i
    ON b.id = i.book_id 
    AND i.settled = 1
WHERE 
    b.deleted = 0
GROUP BY
    b.id

Answer 2

WHERE上的condition and settled = 1有效地将您的LEFT JOIN变成了INNER JOIN。您可以向COUNT中添加CASE表达式

SELECT  b.id, 
        b.title, 
        COUNT(CASE WHEN i.settled = 1 THEN 1 END)
FROM book b
LEFT JOIN invoice i
    ON b.id = i.book_id
WHERE b.deleted=0
GROUP BY b.id;

或者使用已过滤发票表的LEFT JOIN：

SELECT  b.id, 
        b.title, 
        COUNT(i.id)
FROM book b
LEFT JOIN ( SELECT *
            FROM invoice
            WHERE settled = 1) i
    ON b.id = i.book_id
WHERE b.deleted=0
GROUP BY b.id;