SQL - 连接两个表并计算项目
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4075018/
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 - Joining two tables and counting items
提问by tim
Alright, I'm trying to see how many products are provided by each supplier (Products and Suppliers being separate tables). I want the results to be displayed with the company name and the number of products that company has available. I'm not sure exactly how to set this up.
好的，我想看看每个供应商提供了多少产品（产品和供应商是单独的表）。我希望结果与公司名称和公司可用的产品数量一起显示。我不确定如何设置它。
So far I have:
到目前为止，我有：
SELECT CompanyName, Count(ProductName) FROM Suppliers
left join Products on Suppliers.SupplierID = Products.SupplierID;
I'm not sure how to make the ProductName count specific to each company. I'd be eternally grateful for any help you might be able to provide me.
我不确定如何使 ProductName 计数特定于每个公司。如果您能给我提供任何帮助，我将永远感激不尽。
回答by Jonathan Leffler
All you are missing is a GROUP BY clause:
您所缺少的只是一个 GROUP BY 子句：
SELECT CompanyName, Count(ProductName)
FROM Suppliers LEFT JOIN Products
ON Suppliers.SupplierID = Products.SupplierID
GROUP BY CompanyName;
The use of LEFT {OUTER} JOIN means that if there are suppliers that do not provide any products, then the join will return a set of NULL values for the columns corresponding to the Products table. The COUNT(ProductName) then counts just the number of non-null values, thus producing the answer 0 for companies that provide no products. Very often, you'd use a regular INNER JOIN and then you'd not see the companies that provide no products.
使用 LEFT {OUTER} JOIN 意味着如果存在不提供任何产品的供应商，则连接将为 Products 表对应的列返回一组 NULL 值。然后 COUNT(ProductName) 只计算非空值的数量，从而为不提供产品的公司生成答案 0。很多时候，您会使用常规的 INNER JOIN，然后您将看不到不提供产品的公司。
