如何在Microsoft SQL Server中自扩展表计算全月份区间累计错误数

核心思路 #

通过自连接生成同一年度、同一部门下所有合法的连续月份区间（起始月份 ≤ 结束月份），再对每个区间内的错误数求和，同时拼接区间内的月份列表。

适用于SQL Server 2017+/PostgreSQL等支持STRING_AGG的数据库 #

SELECT
    t1.Year,
    STRING_AGG(t2.Month, ',') WITHIN GROUP (ORDER BY t2.Month) AS Months,
    t1.Month AS [First Month],
    t3.Month AS [Last Month],
    t1.Department,
    SUM(t2.NumErrors) AS NumErrorsSum
FROM
    YourTableName t1
-- 关联起始到结束区间内的所有月份记录，用于求和
JOIN YourTableName t2
    ON t2.Year = t1.Year
    AND t2.Department = t1.Department
    AND t2.Month BETWEEN t1.Month AND t3.Month
-- 生成所有可能的结束月份（必须≥起始月份）
JOIN YourTableName t3
    ON t3.Year = t1.Year
    AND t3.Department = t1.Department
    AND t3.Month >= t1.Month
GROUP BY
    t1.Year,
    t1.Month,
    t3.Month,
    t1.Department
ORDER BY
    t1.Year,
    t1.Month,
    t3.Month;

适用于MySQL数据库 #

将STRING_AGG替换为GROUP_CONCAT：

SELECT
    t1.Year,
    GROUP_CONCAT(t2.Month ORDER BY t2.Month SEPARATOR ',') AS Months,
    t1.Month AS `First Month`,
    t3.Month AS `Last Month`,
    t1.Department,
    SUM(t2.NumErrors) AS NumErrorsSum
FROM
    YourTableName t1
JOIN YourTableName t2
    ON t2.Year = t1.Year
    AND t2.Department = t1.Department
    AND t2.Month BETWEEN t1.Month AND t3.Month
JOIN YourTableName t3
    ON t3.Year = t1.Year
    AND t3.Department = t1.Department
    AND t3.Month >= t1.Month
GROUP BY
    t1.Year,
    t1.Month,
    t3.Month,
    t1.Department
ORDER BY
    t1.Year,
    t1.Month,
    t3.Month;

注意事项 #

• 替换YourTableName为实际的表名称
• 确保表中(Year, Month, Department)组合唯一，避免重复数据导致计算结果错误
• 若使用其他不支持字符串聚合函数的数据库，可通过递归CTE实现月份拼接，逻辑核心仍为自连接生成区间

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