SQL Server：计算表B中的ID发生在表B中的次数 [英] SQL Server: Count the number of times the ID from table A occurs in table B

问题描述

我有两个表：产品和订单。订单通过ProductID作为外键引用产品。我想知道每个产品已售出多少次，包括只售出一次的产品。我几乎可以使用左连接工作，但仍然给一个计数为一个所有产品的计数，无论它们是否存在于订单表中。

$ b $

 产品|次出售
 Milk | 5 
面包18 
奶酪| 0 
  

...等等。

解决方案

如果你只是做一个 COUNT（*），那么没有订单的产品为1 ... COUNT（o.OrderID），这将只计算具有非空 OrderID 的记录。 / p>

  SELECT p.Product，COUNT（o.OrderID）
 FROM 
产品p LEFT JOIN 
订单o ON o.ProductID = p.ProductID 
 GROUP BY p.Product 
  

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.

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.

解决方案

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.

SELECT p.Product, COUNT(o.OrderID)
FROM
    Products p LEFT JOIN
    Orders o ON o.ProductID = p.ProductID
GROUP BY p.Product

这篇关于SQL Server：计算表B中的ID发生在表B中的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！