MySQL 在 SQL 列中查找最频繁的值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12235595/
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 most frequent value in SQL column
提问by Jake
How can I find the most frequent value in a given column in an SQL table?
如何在 SQL 表的给定列中找到最频繁的值？
For example, for this table it should return twosince it is the most frequent value:
例如，对于这个表，它应该返回，two因为它是最常见的值：
one
two
two
three
回答by Mihai Stancu
SELECT `column`,
COUNT(`column`) AS `value_occurrence`
FROM `my_table`
GROUP BY `column`
ORDER BY `value_occurrence` DESC
LIMIT 1;
Replace columnand my_table. Increase 1if you want to see the Nmost common values of the column.
替换column和my_table。1如果要查看列的N最常见值，请增加。
回答by Mat
Try something like:
尝试类似：
SELECT `column`
FROM `your_table`
GROUP BY `column`
ORDER BY COUNT(*) DESC
LIMIT 1;
回答by naveen
Let us consider table name as tblpersonand column name as city. I want to retrieve the most repeated city from the city column:
让我们考虑表名tblperson和列名city。我想从城市列中检索重复次数最多的城市：
select city,count(*) as nor from tblperson
group by city
having count(*) =(select max(nor) from
(select city,count(*) as nor from tblperson group by city) tblperson)
Here noris an alias name.
这nor是一个别名。
回答by Swadhikar C
Below query seems to work good for me in SQL Server database:
下面的查询在 SQL Server 数据库中似乎对我有用：
select column, COUNT(column) AS MOST_FREQUENT
from TABLE_NAME
GROUP BY column
ORDER BY COUNT(column) DESC
Result:
结果：
column MOST_FREQUENT
item1 highest count
item2 second highest
item3 third higest
..
..
回答by Muneeb Hassan
For use with SQL Server.
用于 SQL Server。
As there is no limit command support in that.
因为那里没有限制命令支持。
Yo can use the top 1 command to find the maximum occurring value in the particular column in this case (value)
在这种情况下，您可以使用 top 1 命令查找特定列中出现的最大值（值）
SELECT top1
`value`,
COUNT(`value`) AS `value_occurrence`
FROM
`my_table`
GROUP BY
`value`
ORDER BY
`value_occurrence` DESC;
回答by Muzammel Mukul
Assuming Table is 'SalesLT.Customer' and the Column you are trying to figure out is 'CompanyName' and AggCompanyNameis an Alias.
假设 Table 是 ' SalesLT.Customer' 并且您试图找出的 Column 是 ' CompanyName' 并且AggCompanyName是一个别名。
Select CompanyName, Count(CompanyName) as AggCompanyName from SalesLT.Customer
group by CompanyName
Order By Count(CompanyName) Desc;
回答by Mayur Mane
If you have an ID column and you want to find most repetitive category from another column for each ID then you can use below query,
如果您有一个 ID 列，并且您想从每个 ID 的另一列中找到最重复的类别，那么您可以使用以下查询，
Table:
桌子：
Query:
询问：
SELECT ID, CATEGORY, COUNT(*) AS FREQ
FROM TABLE
GROUP BY 1,2
QUALIFY ROW_NUMBER() OVER(PARTITION BY ID ORDER BY FREQ DESC) = 1;
Result:
结果：
回答by Roadkill
If you can't use LIMIT or LIMIT is not an option for your query tool. You can use "ROWNUM" instead, but you will need a sub query:
如果您不能使用 LIMIT 或 LIMIT 不是您的查询工具的选项。您可以改用“ROWNUM”，但您需要一个子查询：
SELECT FIELD_1, ALIAS1
FROM(SELECT FIELD_1, COUNT(FIELD_1) ALIAS1
FROM TABLENAME
GROUP BY FIELD_1
ORDER BY COUNT(FIELD_1) DESC)
WHERE ROWNUM = 1
回答by Omar Lari
One way I like to use is:
我喜欢使用的一种方法是：
select ,COUNT()as VAR1 from Table_Name
选择 ，数数（) 作为来自 Table_Name 的 VAR1
group by
通过...分组
order by VAR1 desc
按 VAR1 desc 排序
limit 1
限制 1
