mysql - mysql 查询出错。需要获取城市名称和在该城市进行的最大比赛数

Question

select host_city,max(city) as res
from (
  select count(host_city) as city
  from match_results 
  group by host_city
) a
LIMIT 0, 1000   

当我的表中确实存在 host_city 列时，我收到此错误。

Error Code: 1054. Unknown column 'host_city' in 'field list'.

我在mysql中这样做

Answer 1

您正在执行SELECT FROM 子SELECT。子选择基本上成为您的表，并且您的子选择没有 host_city 作为字段。这就是您需要的(如果您确实需要 MAX - 我不确定我是否遵循您的逻辑)

select host_city,max(city) as res
from (
    select host_city, count(host_city) as city
    from match_results 
    group by host_city
) a
GROUP BY host_city
LIMIT 0, 1000