SQL Server：统计相同记录组数量并获取组内首个ID

1. 正确获取组内首个Id及计数的SQL写法 #

要获取Column A、Column B、Column C完全相同的组的首个Id（默认按Id升序取最小Id）和组内记录数，以下两种方案中，窗口函数的IO效率更优，避免了多次扫描表：

方法一：窗口函数（IO最优） #

窗口函数只需扫描表一次，直接完成分组计算：

SELECT ColumnA, ColumnB, ColumnC, Id AS FirstId, CommonCount
FROM (
    SELECT 
        ColumnA,
        ColumnB,
        ColumnC,
        Id,
        COUNT(*) OVER (PARTITION BY ColumnA, ColumnB, ColumnC) AS CommonCount,
        ROW_NUMBER() OVER (PARTITION BY ColumnA, ColumnB, ColumnC ORDER BY Id) AS rn
    FROM your_table_name
) t
WHERE rn = 1;

如果你的数据库支持FIRST_VALUE，也可以用DISTINCT简化写法：

SELECT DISTINCT
    ColumnA,
    ColumnB,
    ColumnC,
    FIRST_VALUE(Id) OVER (PARTITION BY ColumnA, ColumnB, ColumnC ORDER BY Id) AS FirstId,
    COUNT(*) OVER (PARTITION BY ColumnA, ColumnB, ColumnC) AS CommonCount
FROM your_table_name;

方法二：分组聚合+关联查询（兼容旧版数据库） #

如果数据库不支持窗口函数，可先分组得到每个组的最小Id和计数，再关联原表：

SELECT 
    t.ColumnA,
    t.ColumnB,
    t.ColumnC,
    g.MinId AS FirstId,
    g.CommonCount
FROM (
    SELECT 
        ColumnA,
        ColumnB,
        ColumnC,
        MIN(Id) AS MinId,
        COUNT(*) AS CommonCount
    FROM your_table_name
    GROUP BY ColumnA, ColumnB, ColumnC
) g
JOIN your_table_name t 
  ON g.ColumnA = t.ColumnA 
  AND g.ColumnB = t.ColumnB 
  AND g.ColumnC = t.ColumnC 
  AND g.MinId = t.Id;

该方案需要扫描表两次，IO效率低于窗口函数。

2. IO优化核心要点 #

• 建立联合覆盖索引：给ColumnA, ColumnB, ColumnC建立联合索引，同时包含Id字段，比如MySQL语法：CREATE INDEX idx_group ON your_table_name (ColumnA, ColumnB, ColumnC, Id);，这样分组或窗口函数计算时直接从索引取数，无需回表，大幅降低IO开销。
• 优先使用窗口函数，避免多次表关联或重复扫描。

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