我在Oracle(12c)中有一个表,它有很多列,其中一个表列是"Color"Color"不是该表主键的一部分。当前"颜色"列的值如下:
Color Column Values
red
red
green
green
green
blue
blue
blue
blue
pink
我需要两个以上数据的信息
i) 。输出低于
color Freq
red 2
green 3
blue 4
pink 1
ii)。表格数据中"颜色"的最高频率,即
blue 4
如何获得这些输出?
这是一个简单的聚合加上一个窗口聚合来查找最大值:
select color, freq
from
( select
color,
count(*) freq,
max(count(*)) over () as max_freq
from tab
group by color
) dt
where freq = max_freq
在Oracle 12c上,您可以对前n个查询使用新语法:
https://oracle-base.com/articles/12c/row-limiting-clause-for-top-n-queries-12cr1#top-n
这种新语法类似于其他数据库中的SELECT TOP x
或LIMIT x
。
SELECT Color, count(*)
FROM table1
GROUP BY Color
ORDER BY count(*) DESC
FETCH FIRST ROW ONLY;
对于i
select color, count(*) freq
from your_table
group by color
对于ii
select color, freq
from
(
select color, freq, max(freq) over() max_freq
from
(
select color, count(*) freq
from your_table
group by color
)
)
where freq = max_freq