SQL去重并统计重复行数问题及CTE代码优化咨询

我有一个包含两列的表MyTable，需求是返回Col_A的唯一值（保留其他列内容），同时统计每个Col_A对应的总行数。预期结果中每个Col_A仅显示组内首条记录的Col_B，附带该行数统计。尝试使用ROW_NUMBER()窗口函数未达成效果（Col_B的值无关紧要）。此外附上实际业务中使用的CTE分页查询代码，需解决该场景下的去重与统计问题。

示例表 #

Col_A | Col_B 
------+-------
A     | 1 
A     | 1 
A     | 2 
A     | 3 
b     | 4 
b     | 4 
b     | 5 

预期结果 #

Col_A   | Col_B | count
--------+-------+-------
A       | 1     | 4 
b       | 4     | 3 

尝试的代码 #

SELECT
    *,  
    ROW_NUMBER() OVER (PARTITION BY Col_A ORDER BY Col_A)  
FROM
    MyTable

基础场景解决方案 #

要实现需求，需结合ROW_NUMBER()筛选组内首条记录，同时用COUNT()统计分组总行数，以下两种方式均可实现：

方法1：CTE方式 #

WITH GroupedData AS (
    SELECT
        Col_A,
        Col_B,
        -- 按任意顺序取组内首条，若需指定排序可替换(SELECT NULL)为具体字段
        ROW_NUMBER() OVER (PARTITION BY Col_A ORDER BY (SELECT NULL)) AS rn,
        COUNT(*) OVER (PARTITION BY Col_A) AS count
    FROM MyTable
)
SELECT Col_A, Col_B, count
FROM GroupedData
WHERE rn = 1;

方法2：子查询方式 #

SELECT Col_A, Col_B, count
FROM (
    SELECT
        Col_A,
        Col_B,
        ROW_NUMBER() OVER (PARTITION BY Col_A ORDER BY (SELECT NULL)) AS rn,
        COUNT(*) OVER (PARTITION BY Col_A) AS count
    FROM MyTable
) t
WHERE rn = 1;

说明：如果需要指定取组内特定顺序的Col_B（比如最早/最晚值），可将ORDER BY (SELECT NULL)改为ORDER BY Col_B ASC或DESC。

实际业务CTE分页场景解决方案 #

针对你提供的分页CTE代码，调整后可实现按ReportID去重、保留组内首条记录，并同时完成统计与分页：

WITH PagedReports AS (
    SELECT
        rm.ReportID,
        rm.ReportName,
        rm.Status,
        rm.ReportDate,
        rm.approvedAmount,
        rm.claimedAmount,
        rm.currency,
        rm.BillApprovalStatus,
        -- 全局分页行号
        ROW_NUMBER() OVER (ORDER BY rm.ReportID) AS RowNumPaging,
        -- 按ReportID分组的行号，用于筛选组内首条
        ROW_NUMBER() OVER (PARTITION BY rm.ReportID ORDER BY rm.ReportName) AS RowNum,
        -- 预统计每个ReportID的账单数和总金额
        COUNT(*) OVER (PARTITION BY rm.ReportID) AS NoOfBills,
        SUM(rm.claimedAmount) OVER (PARTITION BY rm.ReportID) AS TotalAmount
    FROM [dbo].[UserInfo] ui
    INNER JOIN [dbo].[UserReportMap] urm ON ui.SecureDbID = CONVERT(VARCHAR(50), urm.UserID)
    INNER JOIN [dbo].[ReportMaster] rm ON urm.ReportID = rm.ID
    WHERE ui.SecureDbID = 18
)
SELECT
    ReportID,
    ReportName,
    Status,
    ReportDate,
    approvedAmount,
    claimedAmount,
    currency,
    NoOfBills,
    TotalAmount,
    BillApprovalStatus
FROM PagedReports
WHERE 
    RowNum = 1 -- 筛选每个ReportID的首条记录
    AND RowNumPaging BETWEEN 1 AND 100; -- 分页范围

说明：

1. 将统计逻辑移至外层CTE，避免重复计算；
2. 通过RowNum = 1确保每个ReportID仅返回一条记录；
3. 保留原分页条件实现数据分页；
4. 若需调整组内取数顺序，修改PARTITION BY rm.ReportID ORDER BY rm.ReportName中的排序字段即可。

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