Question

我的任务是计算有意见的用户数量;X。

我要问:

SELECT users.id,
       users.display_name, 
       (SELECT COUNT(*) 
          FROM cms_comments 
         WHERE cms_comments.author_id = users.id) AS comments_count 
  FROM users 
HAVING comments_count > 150;

一切照亮,都正确显示所有用户。但是,我需要问一下,是否将所有这些用户的数量逐年回来。我不知道如何改变这一询问,以便得出正确的数据。

Answer 1

我认为,这是你回顾的:

select count(*) from (
    select u.id from users u
    join cms_comments c on u.id = c.author_id
    group by u.id
    having count(*) > 150
) final

Answer 2

按条款使用群体

SELECT users.id,
       users.display_name, 
       (SELECT COUNT(*) 
          FROM cms_comments 
         WHERE cms_comments.author_id = users.id) AS comments_count 
FROM users 
GROUP BY users.id, user.display_name
HAVING comments_count > 150;

这将使每个用户(即用户)都能得到计算。显示:名称有点:打字;150

至于你们关于使用户总数达到最佳程度以更新问题的意见,但如果你们想把符合这一标准的所有用户计算在内,则使用这一标准。

SELECT COUNT(*) AS TotalNumberOfUsersMatchingCritera
FROM
(
    SELECT users.id,
           users.display_name, 
           (SELECT COUNT(*) 
              FROM cms_comments 
             WHERE cms_comments.author_id = users.id) AS comments_count 
    FROM users 
    GROUP BY users.id, user.display_name
    HAVING comments_count > 150;
) AS T

友情链接