oracle 在一个sql命令中从一个数据库表中选择多个计数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4935389/
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
Select multiple counts from one database table in one sql command
提问by endorphin
I have a Oracle database table like so, which records an item being scanned at a scanning point.
我有一个像这样的 Oracle 数据库表，它记录了在扫描点扫描的项目。
ItemsScan
ItemScanId
ItemScanPoint
ItemType
ScanTime
ItemsScan
ItemScanId
ItemScanPoint
ItemType
ScanTime
I want to return the ItemScanPoint along with the number of times a specific ItemType was scanned at that ItemScanPoint.
我想返回 ItemScanPoint 以及在该 ItemScanPoint 扫描特定 ItemType 的次数。
Something along the lines of..
类似的东西..
SELECT ItemScanPoint,
(SELECT COUNT(*) WHERE ItemType = 1),
(SELECT COUNT(*) WHERE ItemType = 2)
FROM ItemsScan
How do I do this in oracle?
我如何在 oracle 中做到这一点？
What is the most efficient way?
最有效的方法是什么？
回答by remi bourgarel
SELECT ItemScanPoint,
SUM(CASE ItemType WHEN 1 THEN 1 ELSE 0 END) ,
SUM(CASE ItemType WHEN 2 THEN 1 ELSE 0 END)
FROM ItemsScan
GROUP BY ItemScanPoint
回答by FrustratedWithFormsDesigner
Does this work for you?
这对你有用吗？
select count(ItemScanId), itemscanpoint, itemtype
from itemsscan
where itemtype in (1,2)
group by itemtype, itemscanpoint
回答by DwB
Count is a grouping function in oracle. Try this:
Count是oracle中的一个分组函数。尝试这个：
select ItemType, count(ItemType) from ItemsScan group by ItemType
回答by David Faber
It might be possible to do this using COUNT() as an analytic function as well:
也可以使用 COUNT() 作为分析函数来做到这一点：
SELECT DISTINCT ItemScanPoint, ItemType
, COUNT(ItemScanId) OVER (PARTITION BY ItemScanPoint, ItemType)
FROM ItemsScan
It will return the data in a different format than that asked in the OP but it will return results for allvalues of ItemTyperather than just two.
它将以与 OP 中要求的格式不同的格式返回数据，但它将返回所有值的结果，ItemType而不仅仅是两个值。
Hope this helps.
希望这可以帮助。
