MySQL 如何统计表中每个外键ID的实例数？

How to count the number of instances of each foreign-key ID in a table?

提问by mattstuehler

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 ALLrows 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 shouldbe listed in the result set, but "number_of_orders" should be zero.

换句话说，返回Books 表中所有行的每一列，以及一个名为“number_of_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 almostright, 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.

这几乎是正确的，但不完全正确，因为即使一本书从未在 Orders 表中列出，“number_of_orders”也将为 1。此外，鉴于我对 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!

提前致谢！

回答by Fabio

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(*)可以在计数中包含 NULL 值，因为它计算所有行，而COUNT(orders.book_id)不是因为它忽略给定字段中的 NULL 值。

回答by Derek Kromm

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

更改count(*)为count(orders.book_id)

回答by RedFilter

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)

回答by Mohit Tejwani

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;

回答by Carl F.

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