select column, COUNT(column) AS MOST_FREQUENT
from TABLE_NAME
GROUP BY column
ORDER BY COUNT(column) DESC
结果:
column MOST_FREQUENT
item1 highest count
item2 second highest
item3 third higest
..
..
1
让我们将表名称视为 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)
这里 nor 是别名 .
129
用于SQL Server .
由于没有限制命令支持 .
在这种情况下,Yo可以使用top 1命令查找特定列中的最大值(值)
SELECT top1
`value`,
COUNT(`value`) AS `value_occurrence`
FROM
`my_table`
GROUP BY
`value`
ORDER BY
`value_occurrence` DESC;
5
SELECT `column`,
COUNT(`column`) AS `value_occurrence`
FROM `my_table`
GROUP BY `column`
ORDER BY `value_occurrence` DESC
LIMIT 1;
替换 column 和 my_table . 如果要查看列的最常见值 N ,请增加 1 .
0
尝试以下方法:
SELECT `column`
FROM `your_table`
GROUP BY `column`
ORDER BY COUNT(*) DESC
LIMIT 1;
33
假设表是' SalesLT.Customer ' and the Column you are trying to figure out is ' CompanyName ', AggCompanyName 是别名 .
Select CompanyName, Count(CompanyName) as AggCompanyName from SalesLT.Customer
group by CompanyName
Order By Count(CompanyName) Desc;
7 回答
以下查询在SQL Server数据库中似乎对我有用:
结果:
让我们将表名称视为
tblperson
,将列名称视为city
. 我想从城市列中检索最重复的城市:这里
nor
是别名 .用于SQL Server .
由于没有限制命令支持 .
在这种情况下,Yo可以使用top 1命令查找特定列中的最大值(值)
替换
column
和my_table
. 如果要查看列的最常见值N
,请增加1
.尝试以下方法:
假设表是'
SalesLT.Customer
' and the Column you are trying to figure out is 'CompanyName
',AggCompanyName
是别名 .如果您不能使用LIMIT或LIMIT不是您的查询工具的选项 . 您可以改为使用“ROWNUM”,但您需要一个子查询: