SQL Server：统计表A中的ID在表B中出现的次数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9067640/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server: Count the number of times the ID from table A occurs in table B
提问by Petter Brodin
I have two tables: products and orders. Orders references products via ProductID as a foreign key. I want to know how many times each product has been sold, including the product being sold only once. I can almost get it to work using a left join, but that still gives one row with a count of one for all products, regardless of whether they exist in the orders table or not.
我有两个表：产品和订单。订单通过 ProductID 作为外键引用产品。我想知道每种产品已售出多少次，包括仅售出一次的产品。我几乎可以使用左连接让它工作，但这仍然为所有产品提供一行，计数为 1，无论它们是否存在于订单表中。
Is there a way to do this that will have you ending up with something like this?
有没有办法做到这一点，让你结束这样的事情？
Product | Times sold
Milk | 5
Bread | 18
Cheese | 0
... and so on.
... 等等。
回答by Michael Fredrickson
If you just do a COUNT(*), then you're counting products that have no orders as 1... instead, COUNT(o.OrderID), which will only count the records that have a non-null OrderID.
如果您只执行 a COUNT(*)，那么您将没有订单的产品计为 1... 相反，COUNT(o.OrderID)它将只计算具有非 null 的记录OrderID。
SELECT p.Product, COUNT(o.OrderID)
FROM
Products p LEFT JOIN
Orders o ON o.ProductID = p.ProductID
GROUP BY p.Product
回答by Tony Hopkinson
Something like
就像是
Select Products.ProductName, Count(Orders.OrderID)
From Orders Inner join on Products Where Orders.ProductID = Products.ProductID
Group By Products.ProductName
回答by Hogan
@Michael is correct.
@迈克尔是对的。
If you have an order table with a count it would look like this:
如果您有一个带有计数的订单表，它将如下所示：
SELECT p.Product, SUM(ISNULL(o.ItemCount,0)) as [Count]
FROM
Products p LEFT JOIN
Orders o ON o.ProductID = p.ProductID
GROUP BY p.Product
