SQL Server：计算表A中的ID在表B中出现的次数

发布于 2024-12-31 22:54:31 字数 255 浏览 0 评论 0原文

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

有没有办法做到这一点，让你最终得到这样的结果？

Product | Times sold
Milk    | 5
Bread   | 18
Cheese  | 0

... 等等。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

惯饮孤独 2025-01-07 22:54:31

如果您只执行 COUNT(*)，那么您会将没有订单的产品计数为 1...，而不是 COUNT(o.OrderID)，这将只计算具有非空 OrderID 的记录。

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

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

回复收藏 0 原文

幸福％小乖 2025-01-07 22:54:31

@迈克尔是正确的。

如果您有一个带有计数的订单表，它将如下所示：

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

@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

回复收藏 0 原文

橪书 2025-01-07 22:54:31

像这样的东西

Select Products.ProductName, Count(Orders.OrderID)
From Orders Inner join on Products Where Orders.ProductID = Products.ProductID
Group By Products.ProductName

Something like

Select Products.ProductName, Count(Orders.OrderID)
From Orders Inner join on Products Where Orders.ProductID = Products.ProductID
Group By Products.ProductName

回复收藏 0 原文

~没有更多了~