SQL 如何基于 RollNo 用 COUNT 统计含 InternalStaff_2 的重复记录

原查询的分组粒度过细：你将InternalStaff_1、InternalStaff_2全部纳入GROUP BY维度，导致相同RollNo但InternalStaff_2取值不同的记录会被拆为独立分组。以Adam的两条记录为例，拆出的两个分组各自计数仅为1，自然无法通过HAVING COUNT(S.RollNo) > 1的过滤条件，最终没有出现在结果中。
你的实际需求是*以RollNo为核心统计维度，只要同一个RollNo总出现次数大于1，就纳入结果；当同RollNo下的InternalStaff_2取值不统一时，该字段返回NULL，而非拆为多行展示。

核心逻辑拆分为两步：

• 第一步：先以RollNo为唯一分组维度，统计每个RollNo的总出现次数，过滤出总次数大于1的RollNo清单
• 第二步：将原始表与过滤后的RollNo清单关联，对同RollNo下的其他字段做聚合：如果某字段在同组内取值完全统一，返回实际值；如果存在多值差异，对有差异的字段返回NULL，匹配期望的输出规则。

调整后的查询语句：

SELECT
    S.Name,
    S.RollNo,
    dup_stats.[Count],
    S.DeptType,
    S.InternalStaff_1,
    -- 同组InternalStaff_2值完全统一时返回实际值，存在差异则返回NULL
    CASE
        WHEN MIN(S.InternalStaff_2) = MAX(S.InternalStaff_2) THEN MIN(S.InternalStaff_2)
        ELSE NULL
    END AS InternalStaff_2
FROM DataMining.dbo.Studies S
INNER JOIN (
    -- 预统计所有重复出现的RollNo及对应计数
    SELECT
        RollNo,
        COUNT(RollNo) AS [Count]
    FROM DataMining.dbo.Studies
    WHERE StartDate >= '20210325'
      AND StartDate <= '20210407'
    GROUP BY RollNo
    HAVING COUNT(RollNo) > 1
) AS dup_stats
    ON S.RollNo = dup_stats.RollNo
WHERE S.StartDate >= '20210325'
  AND S.StartDate <= '20210407'
GROUP BY
    S.Name,
    S.RollNo,
    dup_stats.[Count],
    S.DeptType,
    S.InternalStaff_1
ORDER BY S.RollNo

执行上述查询后，输出与期望结果完全对齐：

• Anu（RollNo=5）的两条记录所有字段取值完全统一，InternalStaff_2返回Antony，计数为2
• Adam（RollNo=2）的两条记录因InternalStaff_2存在Lisa、Kim两个不同取值，字段返回NULL，计数为2
• 彻底解决原查询因分组粒度过细导致的漏统计问题。

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