以列形式查看 SQL 计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2529490/
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 Count in View as column
提问by Alex
I'm trying to get the result of a COUNT as a column in my view. Please see the below query for a demo of the kind of thing I want (this is just for demo purposes)
我试图将 COUNT 的结果作为我认为的一列。请参阅下面的查询以获取我想要的那种东西的演示（这仅用于演示目的）
SELECT
ProductID,
Name,
Description,
Price,
(SELECT COUNT(*) FROM ord WHERE ord.ProductID = prod.ProductID) AS TotalNumberOfOrders
FROM tblProducts prod
LEFT JOIN tblOrders ord ON prod.ProductID = ord.ProductID
This obviously isn't working... but I was wondering what the correct way of doing this would be?
这显然不起作用......但我想知道这样做的正确方法是什么？
I am using SQL Server
我正在使用 SQL Server
采纳答案by Dexter
Your query would actually work if you removed the join - it's not actually used, and it will cause the ord table within the inner select subqueryto conflict with the ord table that you've joined to:
如果您删除了连接，您的查询实际上会起作用 - 它实际上并未使用，并且会导致内部选择子查询中的 ord 表与您加入的 ord 表发生冲突：
SELECT
ProductID,
Name,
Description,
Price,
(SELECT COUNT(*) FROM ord WHERE ord.ProductID = prod.ProductID) AS TotalNumberOfOrders
FROM tblProducts prod
Alternatively, you can actually make use of the joined table in conjunction with Group By:
或者，您实际上可以结合使用连接表Group By：
SELECT
ProductID,
Name,
Description,
Price,
COUNT(ord.ProductID) AS TotalNumberOfOrders
FROM tblProducts prod
LEFT JOIN tblOrders ord ON prod.ProductID = ord.ProductID
GROUP BY
ProductID,
Name,
Description,
Price
回答by SqlACID
Try this:
尝试这个：
SELECT
ProductID,
Name,
Description,
Price,
count(*) as totalnumberoforders
FROM tblProducts prod
LEFT JOIN tblOrders ord ON prod.ProductID = ord.ProductID
group by
ProductID,
Name,
Description,
Price
回答by Enrico Campidoglio
If you are only interested in the products that have been ordered, you could simply substitute the LEFT OUTER JOIN operation with an INNER JOIN:
如果您只对已订购的产品感兴趣，您可以简单地将 LEFT OUTER JOIN 操作替换为 INNER JOIN：
SELECT
prod.ProductID,
prod.Name,
prod.Description,
prod. Price,
COUNT(*) AS TotalNumberOfOrders
FROM tblProducts prod
INNER JOIN tblOrders ord ON prod.ProductID = ord.ProductID
回答by Philip Kelley
This will work:
这将起作用：
SELECT
ProductID,
Name,
Description,
Price,
COUNT(ord.ProductId) AS TotalNumberOfOrders
FROM tblProducts prod
LEFT JOIN tblOrders ord
ON prod.ProductID = ord.ProductID
GROUP BY
ProductID,
Name,
Description,
Price
The "COUNT(ord.ProductId)" clause ensures that if no orders are found, TotalNumberOfOrders will be equal to zero.
"COUNT(ord.ProductId)" 子句确保如果没有找到订单，TotalNumberOfOrders 将等于零。
[Later edit: I forgot the GROUP BY clause. Doh!]
[后期编辑：我忘记了 GROUP BY 子句。呸！]
