如何计算表中每个外键ID的实例数? [英] How to count the number of instances of each foreign-key ID in a table?

问题描述

这是我简单的SQL问题...

Here's my simple SQL question...

我有两个表:

书籍

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

订单

------------------------------------
| 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 |
--------------------------------------------------------------------------

换句话说，返回书籍"表中 ALL 行的每一列，以及一个计算得出的名为"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.

到目前为止，我已经提出了这个建议:

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

这几乎是对的，但不是完全正确的，因为即使从未在Orders表中列出一本书，"number_of_orders"也将为1.而且，由于我缺乏SQL知识，因此我确定此查询的效率非常低.

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.

编写此查询的正确方法是什么? (就其价值而言，这需要在MySQL上运行，因此我不能使用任何其他供应商特定的功能.)

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).

提前谢谢！

推荐答案

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

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(*)可能在计数中包含NULL值，因为它对所有行进行计数，而COUNT(orders.book_id)并非因为它忽略给定字段中的NULL值.

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.

这篇关于如何计算表中每个外键ID的实例数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！