mysql - 显示出现次数最多的值

我有两个相关的表：

（1）人包含名称和图像文件。

（2）城市包含他们访问过的城市。

人

id name     image
1  John     NULL
2  Carrie   001.jpg
3  Desmond  002.jpg
4  Harry    003.jpg
5  Paul     NULL

城市

id  city      people_id   year_visited
1   Chicago   1           2000
2   Chicago   4           2000
3   Chicago   5           2001
4   Paris     1           2000
5   Paris     2           2002
6   Chicago   4           2002
7   Chicago   1           2001
8   London    1           2004
9   Sydney    5           2001
10  Sydney    1           2002
11  Rio       5           2002
12  London    5           2004
13  Sydney    5           2003
14  Sydney    5           2005

我想找出所有没有图像的人，以及他们访问过最多的城市。所以我想要的结果是：

name  most_visited_city number_of_visits
John  Chicago           2
Paul  Sydney            3

我可以group_concat他们访问过的城市，但不能深入到他们访问最多的单个城市。

感谢所有帮助。

最佳答案

以下是人员，城市和人数的信息：

select p.id, c.city, count(*) as cnt
from people p join
     cities c
     on p.id = c.people_id
where p.image is null
group by p.id, c.city;

在MySQL中，获取有关访问量最大的信息非常棘手。如果数据不是太大，这是一种可行的方法：

select id,
        substring_index(group_concat(city order by cnt desc separator '|'), '|', 1) as most_visited_city,
        max(cnt) as number_of_times_visited
from (select p.id, c.city, count(*) as cnt
      from people p join
           cities c
           on p.id = c.people_id
      where p.image is null
      group by p.id, c.city
     ) pc
group by id;