mysql - SQL 计数不同的行

Question

我有一个这样的表:

每一行代表用户给出的评论。我想通过他们撰写评论的不同城市的数量来吸引用户。例如:

城市，用户

1,1012388

2,221758

这意味着 1012388 位用户仅在一个城市为商家撰写了评论。

我试过:

select count(distinct(city)) as city, count(user_id) 
from table1
group by user_id 
order by count(user_id) desc

Answer 1

首先您需要找到每个用户写的不同城市评论的数量:

SELECT user_id, COUNT(DISTINCT city) AS num_reviews
FROM table1
GROUP BY user_id

然后您可以按评论数量对该数据进行分组:

SELECT num_reviews, COUNT(user_id) AS num_users
FROM (SELECT user_id, COUNT(DISTINCT city) AS num_reviews
      FROM table1
      GROUP BY user_id) r
GROUP BY num_reviews
ORDER BY num_users DESC