SQL Server 2014按模块分组取Top2记录并汇总计数的实现方法

嘿，我来帮你搞定这个查询问题！先说说你原来的思路偏差在哪里：

你的原始查询直接在未聚合的原始数据上使用窗口函数，这就导致每条原始记录都会被单独处理，所以才会出现大量重复的行。而且你的RANK()函数排序逻辑也不对——你应该按每个id_cr的计数来排序，而不是id_module。

要实现你想要的“按id_module分组，取每个组内出现次数最多的前2个id_cr及其计数”，我们需要分三步走：

步骤1：先统计每个(id_module, id_cr)组合的出现次数 #

首先得把重复的记录合并，计算出每个模块下每个id_cr的总次数，这一步要用GROUP BY来聚合。

步骤2：给每个模块内的记录按计数排名 #

在聚合后的结果上，用窗口函数给每个id_module分组内的记录按计数降序排名。

步骤3：筛选出每个模块的前2条记录 #

基于排名结果，筛选出排名≤2的记录即可。

下面是完整的SQL查询语句：

WITH CrCounts AS (
    -- 第一步：统计每个模块下每个id_cr的出现次数
    SELECT 
        id_module,
        id_cr,
        COUNT(*) AS cr_count
    FROM webtracker_user
    WHERE id_module > 0 AND id_cr > 0
    GROUP BY id_module, id_cr
),
RankedCr AS (
    -- 第二步：给每个模块内的记录按计数排名
    SELECT 
        id_module,
        id_cr,
        cr_count,
        -- 用DENSE_RANK可以保留并列的排名，比如两个id_cr计数相同都算前2
        DENSE_RANK() OVER (PARTITION BY id_module ORDER BY cr_count DESC) AS rank_num
    FROM CrCounts
)
-- 第三步：筛选出每个模块前2的记录
SELECT 
    id_module AS 'Grouped by id_module',
    id_cr AS 'Grouped by id_cr',
    cr_count AS 'Count (sum) of id_cr'
FROM RankedCr
WHERE rank_num <= 2
ORDER BY id_module, cr_count DESC;

为什么这个查询能满足你的需求？ #

• CrCounts CTE先把重复的记录聚合，得到每个(id_module, id_cr)组合的准确计数，从根源上避免了原始查询中的重复行问题。
• RankedCr CTE用DENSE_RANK()（你也可以换成RANK()，区别是DENSE_RANK会给相同计数的记录相同排名，且排名不会跳号）按计数降序给每个模块内的记录排名，确保高频记录排在前面。
• 最后筛选排名≤2的记录，就得到了你想要的每个模块下Top 2的高频id_cr。

用你提供的测试数据运行这个查询，会得到和你预期完全一致的结果：

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