select x.*
from (select val,
count(*) as cnt,
row_number() over (order by count(*) desc ) as seqnum,
count(*) over () as num_vals
count(*) over (partition by count(*)) as cnt_cnt
from table
group by val
) x
where cnt_cnt = 1 and seqnum = 1 and num_vals > 1;
实际上,您可以使用having子句和order by来实现:
select val
from (select val, count(*) as cnt,
count(*) over () as num_values
from table
group by val
) v
where num_values > 1
order by cnt desc;
您可以计算值的数量,找出频率最高的值,并且还可以根据值的数量进行筛选:
实际上,您可以使用
having
子句和order by
来实现: