SQL 选择最大计数 [英] SQL SELECT MAX COUNT

问题描述

我在一个表中有三列:id、streetname、count.对某些 ID 而言，分配的街道名称不止一个.Count 表示将相应街道分配给 id 的频率.我如何才能获得计数最高的 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 选择最大计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！