SQL SELECT MAX COUNT [英] SQL SELECT MAX COUNT

问题描述

我在表中有三列：id，streetname，count。到一些ids是多个街道名称assinged。计数指示相应街道分配给ID的频率。

I have three columns in a table: id, streetname, count. To some ids is more than one streetname assinged. Count tells how often the respective street is assigned to the id. How can I get just the id and the streetname with the highest count.

示例表：

id     streetname     count
1      street1        80
1      street2        60
1      street3        5
2      street4        10
2      street5        6

结果应该是这样：

id     streetname
1      street1
2      street4

！

推荐答案

您没有指定要使用的数据库，但您应该能够使用以下方法：

You did not specify what database you are using but you should be able to use the following:

select t1.id, t1.streetname, t1.count
from yourtable t1
inner join
(
  select id, max(count) max_count
  from yourtable
  group by id
) t2
  on t1.id = t2.id
  and t1.count = t2.max_count

查看 SQL Fiddle with Demo 。注意，您必须使用MySQL的反引号或数据库用于转义保留字的任何字符来转义 count 列名。我的建议是避免对列和表名使用保留字。

See SQL Fiddle with Demo. Note, you will have to escape the count column name using backticks for MySQL or whatever character your database uses to escape reserved words. My suggestion would be to avoid using reserved words for column and table names.

这篇关于SQL SELECT MAX COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！