频率报告的MySQL频率

Question

考虑到许多用户有很多帖子

我有一个有外键user_id的帖子表

我想要生成一份报告，显示用户的频率和帖子的频率。

例如：

3个用户分别写了2篇文章

2名用户各写1篇文章

1个用户写了4个帖子

Number of users | Number of posts
--------------- | ------------------
1               | 4
2               | 1
3               | 2  

我的尝试：

SELECT inner_table.frequency_posts, 
       Count(*) AS frequency_users
FROM   posts 
       INNER JOIN (SELECT user_id, 
                          Count(*) AS frequency_posts 
                   FROM   posts
                   GROUP  BY user_id) AS inner_table 
               ON posts.user_id = inner_table.user_id
GROUP  BY inner_table.frequency_posts

我认为frequency_posts是有效的，但计数frequency_users并不能给出正确的值--当我自己查看内部选择并手动将帖子相加时，就不会得到相同的值

Answer 1

您必须使用Group by两次：

SELECT     
  COUNT(*) AS NumberOfUsers,
  foo.NumberOfPosts
FROM
    (SELECT 
        p.UserId AS UserId, 
        COUNT(*) AS NumberOfPosts
    FROM
        posts AS p
    GROUP BY UserId) as foo
GROUP BY foo.NumberOfPosts