如何在GROUP BY分组后按COUNT(*)聚合结果取每个分组的前N行数据

你可以直接将窗口函数套用在聚合后的结果上实现需求，以下是兼容所有支持窗口函数的数据库（MySQL8+、PostgreSQL、SQL Server等）的通用写法：

WITH user_create_stats AS (
    -- 先执行原有聚合逻辑，统计每个类型下每个用户的创建记录数
    SELECT 
        recordtype, 
        createdby, 
        COUNT(*) AS create_count
    FROM recordtable
    WHERE recordtype in (...) -- 替换为你需要筛选的recordtype列表
    GROUP BY recordtype, createdby
),
ranked_users AS (
    -- 按recordtype分组，对聚合得到的创建数降序排名
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY recordtype 
            ORDER BY create_count DESC
        ) AS rank_in_type
    FROM user_create_stats
)
-- 过滤取每个类型排名前N的用户，要前10就把5改为10即可
SELECT recordtype, createdby, create_count
FROM ranked_users
WHERE rank_in_type <= 5
ORDER BY recordtype, rank_in_type;

如果需要处理并列排名的场景，可以根据需求替换窗口函数：

• 用RANK()：并列用户占用相同排名位，下一名跳号，比如两个第1名后直接是第3名，取前5时返回结果数可能大于5
• 用DENSE_RANK()：并列用户占用相同排名位，下一名不跳号，比如两个第1名后是第2名，返回结果数比RANK()更多
• 用ROW_NUMBER()：创建数相同时也会强制排序，严格返回每个类型最多N条结果

如果你使用的是不支持CTE和窗口函数的老版本数据库（比如MySQL5.x），可以用变量实现相同逻辑：

SELECT recordtype, createdby, create_count
FROM (
    SELECT 
        t.*,
        @rank := IF(@current_type = recordtype, @rank + 1, 1) AS rank_in_type,
        @current_type := recordtype
    FROM (
        SELECT 
            recordtype, 
            createdby, 
            COUNT(*) AS create_count
        FROM recordtable
        WHERE recordtype in (...)
        GROUP BY recordtype, createdby
        ORDER BY recordtype, create_count DESC
    ) t,
    (SELECT @current_type := '', @rank := 0) vars
) ranked
WHERE rank_in_type <=5
ORDER BY recordtype, rank_in_type;

内容的提问来源于stack exchange，提问作者CarenRose