如何用SQL统计发帖量超3条及超5条的用户数量？

你需要从Users和Posts表中统计两类用户的数量：发帖量超过3条的用户、发帖量超过5条的用户，输出格式要求包含NumberOfUsers（用户数）和Posts（阈值标识）两列。

原SQL的问题 #

你提供的SQL缺少GROUP BY u.Id子句，导致HAVING COUNT(p.Id)>3无法正确筛选单用户的发帖量——没有分组时，COUNT(u.Id)统计的是所有用户-帖子关联记录的总数，而非符合条件的用户数，同时也无法一次性输出两个阈值的统计结果。

正确的SQL实现 #

方法一：子查询+UNION ALL（直观易懂）

SELECT 
    COUNT(*) AS NumberOfUsers,
    '>3' AS Posts
FROM (
    -- 筛选发帖量>3的用户ID
    SELECT u.Id
    FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerId
    GROUP BY u.Id
    HAVING COUNT(p.Id) > 3
) AS users_over_3

UNION ALL

SELECT 
    COUNT(*) AS NumberOfUsers,
    '>5' AS Posts
FROM (
    -- 筛选发帖量>5的用户ID
    SELECT u.Id
    FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerId
    GROUP BY u.Id
    HAVING COUNT(p.Id) > 5
) AS users_over_5

方法二：条件聚合（更高效，减少一次分组）

SELECT
    SUM(CASE WHEN post_count > 3 THEN 1 ELSE 0 END) AS NumberOfUsers,
    '>3' AS Posts
FROM (
    -- 先统计每个用户的发帖量
    SELECT u.Id, COUNT(p.Id) AS post_count
    FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerId
    GROUP BY u.Id
) AS user_post_stats

UNION ALL

SELECT
    SUM(CASE WHEN post_count > 5 THEN 1 ELSE 0 END) AS NumberOfUsers,
    '>5' AS Posts
FROM (
    SELECT u.Id, COUNT(p.Id) AS post_count
    FROM Users u
    INNER JOIN Posts p ON u.Id = p.OwnerId
    GROUP BY u.Id
) AS user_post_stats

两种方法都能输出你需要的格式：

内容的提问来源于stack exchange，提问作者Tomáš grzeho