SQL 如何选择最常出现的值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7705929/
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
how to select the most frequently appearing values?
提问by in His Steps
I've seen examples where the query orders by count and takes the top row, but in this case there can be multiple "most frequent" values, so I might want to return more than just a single result.
我见过查询按计数排序并取顶行的示例，但在这种情况下，可能有多个“最频繁”的值，因此我可能想要返回的不仅仅是一个结果。
In this case I want to find the most frequently appearing last names in a users table, here's what I have so far:
在这种情况下，我想在用户表中找到最常出现的姓氏，这是我目前所拥有的：
select last_name from users group by last_name having max(count(*));
Unfortunately with this query I get an error that my max function is nested too deeply.
不幸的是，在这个查询中我得到一个错误，我的 max 函数嵌套太深。
回答by GolezTrol
select
x.last_name,
x.name_count
from
(select
u.last_name,
count(*) as name_count,
rank() over (order by count(*) desc) as rank
from
users u
group by
u.last_name) x
where
x.rank = 1
Use the analytical function rank. It will assign a numbering based on the order of count(*) desc. If two names got the same count, they get the same rank, and the next number is skipped (so you might get rows having ranks 1, 1 and 3). dense_rankis an alternative which doesn't skip the next number if two rows got the same rank, (so you'd get 1, 1, 2), but if you want only the rows with rank 1, there is not much of a difference.
使用解析函数rank。它将根据 的顺序分配编号count(*) desc。如果两个名称的计数相同，则它们的排名相同，并跳过下一个数字（因此您可能会得到排名为 1、1 和 3 的行）。dense_rank是一种替代方法，如果两行的排名相同，则不会跳过下一个数字（所以你会得到 1、1、2），但如果你只想要排名为 1 的行，则没有太大区别.
If you want only one row, you'd want each row to have a different number. In that case, use row_number. Apart from this small-but-important difference, these functions are similar and can be used in the same way.
如果你只想要一行，你会希望每一行都有不同的数字。在这种情况下，请使用row_number. 除了这个小而重要的区别之外，这些功能是相似的，可以以相同的方式使用。
回答by Arjun Prakash
select name
from
(select name, count(1)
from table
group by name
order by count(1) desc) a
where rownum = 1
