oracle 在 SQL 中查找列中出现次数最多的
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4635796/
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
Find the highest number of occurences in a column in SQL
提问by Ronnie
Given this table:
鉴于此表：
Order
custName description to_char(price)
A desa $14
B desb $14
C desc $21
D desd $65
E dese $21
F desf $78
G desg $14
H desh $21
订单
custName 描述 to_char(price)
A desa $14
B desb $14
C desc $21
D desd $65
E
dese $ 21 F desf $78
G desg $14
H desh $21
I am trying to display the whole row where prices have the highest occurances, in this case $14 and $21
我试图显示价格出现最高的整行，在这种情况下为 14 美元和 21 美元
I believe there needs to be a subquery. So i started out with this:
我相信需要有一个子查询。所以我从这个开始：
select max(count(price))
from orders
group by price
which gives me 3.
这给了我 3。
after some time i didn't think that was helpful. i believe i needed the value 14 and 21 rather the the count so i can put that in the where clause. but I'm stuck how to display that. any help?
一段时间后，我认为这没有帮助。我相信我需要值 14 和 21 而不是计数，所以我可以把它放在 where 子句中。但我不知道如何显示它。有什么帮助吗？
UPDATE: So I got it to query the 14 and 21 from this
更新：所以我让它从这个查询 14 和 21
select price
from orders
group by price
having (count(price)) in
(select max(count(price))
from orders
group by price)
but i need it to display the custname and description column which i get an error:
但我需要它来显示我收到错误的 custname 和 description 列：
select custname, description, price
from orders
group by price
having (count(price)) in
(select max(count(price))
from orders
group by price)
SQL Error: ORA-00979: not a GROUP BY expression
any help on this?
对此有任何帮助吗？
回答by devio
I guess you are pretty close. Since HAVING operates on the GROUPed result set, try
我猜你很接近。由于 HAVING 对 GROUPed 结果集进行操作，请尝试
HAVING COUNT(price) IN
or
或者
HAVING COUNT(price) =
replacing your current line.
替换您当前的行。
回答by Brian
Since you tagged the question as oracle, you can use windowing functionsto get aggregate and detail data within the same query.
由于您将问题标记为 oracle，您可以使用窗口函数在同一查询中获取聚合和详细数据。
SELECT COUNT (price) OVER (PARTITION BY price) count_at_this_price,
o.*
from orders o
order by 1 desc
回答by Jahan
回答by Daro
You could try something like
你可以尝试类似的东西
select * from orders where price in (select top 2 price from orders group by price order by price desc)
I'm not sure of limiting results in Oracle, in SQL Server is top, maybe you should use limit.
我不确定在 Oracle 中限制结果，在 SQL Server 中是最重要的，也许您应该使用限制。
