SQL如何实现按SECTIONNO统计SIZENAME各尺寸的滚动累计计数

你当前的PIVOT语句仅能生成单SECTIONNO分组的尺寸计数，要实现历史累计值，只需在PIVOT结果层叠加累计求和窗口函数即可，修改后的SQL如下：

SELECT 
    SECTIONNO,
    SUM(M) OVER(ORDER BY SECTIONNO) AS M,
    SUM(L) OVER(ORDER BY SECTIONNO) AS L,
    SUM(XL) OVER(ORDER BY SECTIONNO) AS XL,
    SUM([2XL]) OVER(ORDER BY SECTIONNO) AS [2XL]
FROM (
    SELECT SECTIONNO, SIZENAME
    FROM TableNAME
) s 
PIVOT (
    COUNT(SIZENAME) FOR [SIZENAME] IN ([M], [L], [XL], [2XL])
) PVT 
ORDER BY SECTIONNO

代码说明 #

1. 内层逻辑和你原有实现完全一致，先统计每个SECTIONNO下各尺寸的独立计数
2. 外层通过SUM() OVER()窗口函数实现累计：
   • 按SECTIONNO升序排序后，每个尺寸列的求和范围覆盖所有序号小于等于当前行的分组
   • 省略窗口帧声明时，默认的计算范围就是「从首行到当前行」，和显式写ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW效果完全一致
3. 如果你使用的是不支持PIVOT语法的数据库（比如MySQL），也可以用条件聚合加窗口函数的写法实现，逻辑完全等价：

SELECT 
    SECTIONNO,
    SUM(SUM(CASE WHEN SIZENAME='M' THEN 1 ELSE 0 END)) OVER(ORDER BY SECTIONNO) AS M,
    SUM(SUM(CASE WHEN SIZENAME='L' THEN 1 ELSE 0 END)) OVER(ORDER BY SECTIONNO) AS L,
    SUM(SUM(CASE WHEN SIZENAME='XL' THEN 1 ELSE 0 END)) OVER(ORDER BY SECTIONNO) AS XL,
    SUM(SUM(CASE WHEN SIZENAME='2XL' THEN 1 ELSE 0 END)) OVER(ORDER BY SECTIONNO) AS [2XL]
FROM TableNAME
GROUP BY SECTIONNO
ORDER BY SECTIONNO

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