如何计算表中每个外键ID的实例数?

Here's my simple SQL question...

这是我简单的SQL问题……

I have two tables:

我有两个表:

Books

书

-------------------------------------------------------
| book_id | author | genre | price | publication_date |
-------------------------------------------------------

Orders

订单

------------------------------------
| order_id | customer_id | book_id |
------------------------------------

I'd like to create a query that returns:

我想创建一个返回:

--------------------------------------------------------------------------
| book_id | author | genre | price | publication_date | number_of_orders |
--------------------------------------------------------------------------

In other words, return every column for ALL rows in the Books table, along with a calculated column named 'number_of_orders' that counts the number of times each book appears in the Orders table. (If a book does not occur in the orders table, the book should be listed in the result set, but "number_of_orders" should be zero.

换句话说，返回Books表中所有行的每一列，以及一个名为“number_of_orders”的计算列，该列计算每本书出现在Orders表中的次数。(如果书不在orders表中出现，那么书应该在结果集中列出，但是“number_of_orders”应该为零。

So far, I've come up with this:

到目前为止，我想到了这个:

SELECT
    books.book_id,
    books.author,
    books.genre,
    books.price,
    books.publication_date,
    count(*) as number_of_orders
from books
left join orders
on (books.book_id = orders.book_id)
group by
    books.book_id,
    books.author,
    books.genre,
    books.price,
    books.publication_date

That's almost right, but not quite, because "number_of_orders" will be 1 even if a book is never listed in the Orders table. Moreover, given my lack of knowledge of SQL, I'm sure this query is very inefficient.

这几乎是正确的，但并不完全正确，因为“number_of_orders”将是1，即使书从未在Orders表中列出。此外，由于缺乏SQL知识，我确信这个查询非常低效。

What's the right way to write this query? (For what it's worth, this needs to work on MySQL, so I can't use any other vendor-specific features).

编写这个查询的正确方法是什么?(不管怎么说，这需要在MySQL上运行，所以我不能使用任何其他供应商特有的特性)。

Thanks in advance!

提前谢谢!

5 个解决方案

#1

Your query is almost right and it's the right way to do that (and the most efficient)

您的查询几乎是正确的，这是正确的方法(也是最有效的方法)

SELECT books.*, count(orders.book_id) as number_of_orders        
from books
left join orders
on (books.book_id = orders.book_id)
group by
    books.book_id

COUNT(*) could include NULL values in the count because it counts all the rows, while COUNT(orders.book_id) does not because it ignores NULL values in the given field.

COUNT(*)可以在计数中包含空值，因为它计数所有行，而COUNT(orders.book_id)并不是因为它忽略给定字段中的空值。

#2

Change count(*) to count(orders.book_id)

改变count(*)数(orders.book_id)

#3

SELECT b.book_id,
    b.author,
    b.genre,
    b.price,
    b.publication_date,
    coalesce(oc.Count, 0) as number_of_orders
from books b
left join (
    select book_id, count(*) as Count 
    from Order 
    group by book_id
) oc on (b.book_id = oc.book_id)

#4

You're counting the wrong thing. You want to count the non-null book_id's.

你数错了。您需要计算非空book_id。

SELECT
    books.book_id,
    books.author,
    books.genre,
    books.price,
    books.publication_date,
    count(orders.book_id) as number_of_orders
from books
left join orders
on (books.book_id = orders.book_id)
group by
    books.book_id,
    books.author,
    books.genre,
    books.price,
    books.publication_date

#5

select author.aname,count(book.author_id) as "number_of_books"
from author 
left join book 
on(author.author_id=book.author_id)
GROUP BY author.aname;

#1