mysql - 数据透视表的 SQL 查询

Question

我的表格有两列，名称和访问过的国家/地区:

Name   Subjects
mike   england
maria  china
james  russia
mike   mexico
maria  france
laurel england
...    ...

现在我想编写一个查询，显示访问过两个国家、三个国家、四个国家等最多十个国家的人数。

最终结果应该是这样的:

Number of countries -> number of people
10                  -> 5
9                   -> 13
8                   -> 24
and so on.

那么我该如何处理呢？有什么建议吗？

Answer 1

试试这个:

SELECT t2.number_of_country, 
       count(t1.Name) number_of_people
FROM tableName t1
INNER JOIN 
    (SELECT Name, count(Subjects) number_of_country
     FROM tableName 
     GROUP BY Name) t2 ON t2.Name = t1.Name
GROUP BY t2.number_of_country