MySQL 从关系中选择最常见的值 - SQL 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2581795/
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
Selecting the most common value from relation - SQL statement
提问by Ronnie
I have a table within my database that has many records, some records share the same value for one of the columns. e.g.
我的数据库中有一个表，它有很多记录，有些记录共享其中一列的相同值。例如
| id | name | software |
______________________________
| 1 | john | photoshop |
| 2 | paul | photoshop |
| 3 | gary | textmate |
| 4 | ade | fireworks |
| 5 | fred | textmate |
| 6 | bob | photoshop |
I would like to return the value of the most common occurring piece of software, by using an SQL statement.
我想通过使用 SQL 语句返回最常见的软件的值。
So in the example above the required SQL statement would return 'photoshop' as it occurs more than any other piece of software.
因此，在上面的示例中，所需的 SQL 语句将返回“photoshop”，因为它比任何其他软件出现的次数都多。
Is this possible?
这可能吗？
Thank you for your time.
感谢您的时间。
回答by Carl Manaster
select top 1 software
from your_table
group by software
order by count(*) desc
回答by cletus
It depends if you want to use standard SQL or vendor specific extensions (another poster has a "top N" query which is notstandard). A standard solution would be.
这取决于如果你想使用标准的SQL或供应商特定扩展（另一个海报有一个“前N个”查询是不是标准）。一个标准的解决方案是。
SELECT software, COUNT(1)
FROM tablename
GROUP BY software
HAVING COUNT(1) = (
SELECT MAX(sc) FROM (
SELECT software, COUNT(1) sc
FROM tablename
GROUP BY software
)
)
Note:this may return multiple rows if several pieces of software are tied for the most occurrences.
注意：如果多个软件与大多数出现次数相关，这可能会返回多行。
回答by Arush Kamboj
You can do it in many ways but the simplest way would be this
您可以通过多种方式做到这一点，但最简单的方法是
SELECT item
FROM (SELECT item FROM your_table GROUP BY item ORDER BY COUNT(*) desc)
WHERE ROWNUM<=1;
