SQL Server 开窗函数 实现行级数据汇总、列级数据汇总

SQL Server 开窗函数OVER，实现行级数据汇总、列级数据汇总，实例如下：

 

1、创建表（临时表）

创建出测试使用表，并初始化数据：

CREATE TABLE #TMP_ORDER_MONTH (ID     INT,
                               YEAR   INT,  --- 月份
                               MONTH  INT,  --- 月份
                               Amount FLOAT --- 金额
);
--初始数据
INSERT INTO #TMP_ORDER_MONTH
SELECT 1, 2019,  9, 100  UNION ALL
SELECT 2, 2019, 10, 200 UNION ALL
SELECT 3, 2019, 11, 300 UNION ALL
SELECT 4, 2019, 12, 400 UNION ALL
SELECT 5, 2020,  1, 100 UNION ALL
SELECT 6, 2020,  2, 200 UNION ALL
SELECT 7, 2020,  3, 300 UNION ALL
SELECT 8, 2020,  4, 400 UNION ALL
SELECT 9, 2020,  5, 500;
SELECT * FROM #TMP_ORDER_MONTH;

    

 

2、列级汇总数据

进行列级数据汇总：增加累计销售额，按照从上至下的顺序，逐月累加；增总销售额列计算销售总额。

SELECT ID,
       YEAR,
       MONTH,
       Amount,
       SUM(Amount) OVER (PARTITION BY YEAR, MONTH) AS '月销售额',
       SUM(Amount) OVER (ORDER BY YEAR, MONTH ASC) AS '累计销售额',
       SUM(Amount) OVER ()                         AS '总销售额'
  FROM #TMP_ORDER_MONTH;

 结果如下：

    

 

3、行级汇总数据

进行行级数据汇总：增加多显示行，汇总年度业绩和全部业绩。

SELECT      ISNULL (CAST(YEAR AS VARCHAR(10)), '合计') AS YEAR ,
        ISNULL(CAST(MONTH AS VARCHAR(10)) , '合计' ) AS MONTH ,
        SUM(Amount) AS Amount
FROM    #TMP_ORDER_MONTH
GROUP BY YEAR , MONTH WITH ROLLUP ;

  结果如下：

    

 DROP TABLE #TMP_ORDER_MONTH;