SQL server sum() over 累加注意问题

sum over全部累加和逐行累加的用法：

SELECT FROM (

SELECT *,20000 as ReQTY,'发料方式' as SendReelStyle,SUM(ReelQty)

OVER (ORDER BY ReelQty DESC rows BETWEEN unbounded preceding and current row) AS sumReelQtyAsc 

SUM(ReelQty) OVER() AS sumReelQty

FROM [LightLampSystemDB].[dbo].[SaveTable] WHERE ReelQty!=0 AND ReelPartNo='0341222332') AS t 
WHERE sumReelQtyAsc<=20000

ORDER BY ReelQty;

---

SQL SERVER：开窗函数 SUM() OVER() 数据统计中一例使用

前几天，有个朋友刚好问到本人有关 SQL 语句，大致是原表有两列，分别为月份、月份销售额，而需要一条 SQL 语句实现统计出每个月份以及当前月以前月份销售额和。尼玛，感觉还是没有说清，下面用图表示。

SQL 测试表脚本

DECLARE @Temp Table
(
  ID INT, --- 月份
  MoneyData Float  --- 金额 
)

insert  INTO @TEMP
SELECT 1,100 UNION ALL
SELECT  2,200 UNION ALL
SELECT  3,300 UNION ALL
SELECT  4,400 UNION ALL
SELECT  5,500 UNION ALL
SELECT  6,600 UNION ALL
SELECT  7,600

一 自连接

SELECT  A.ID, SUM(B.MoneyData)  FROM @Temp A INNER JOIN @Temp B
ON A.ID>=B.ID GROUP BY A.ID

------ 重点在于 ON 条件，通过自连接 A.ID >= B.ID ，可获取所需数据，再通过GROUP BY 、SUM 即可统计求和

二 开窗函数

SELECT  ID, MoneyData,   SUM(MoneyData) OVER( ) AS '总销售额',
SUM(MoneyData) OVER( PARTITION BY ID ) AS '月销售额',
SUM(MoneyData) OVER( ORDER BY ID ASC)  AS '当月+当月前销售额'
FROM @Temp

---- 此处，使用开窗函数使用更为简单，不过，其中 SUM() OVER ( ORDER BY ID ASC ) 即可实现以上功能

关于，开窗函数的具体用法，可参考微软 MSDN 官方 API 文档，最后，推荐一本开窗函数书：基于 SQL SERVER 2012 窗口函数。

---

sql开窗函数_累计求和开窗函数sum() over()

需求：有如下示例数据，其中PRODUCT表示产品，CONTRACT_MOUTH表示合同月份，AMOUNT表示当月合同金额，请求出每个产品累计合同金额。

比如牛奶2月份累计求和金额为100+200=300，牛奶3月份累计求和金额为100+200+300=600，依此类推。

示例数据sql：

CREATE TABLE SUMOVER(
PRODUCT VARCHAR(50),
CONTRACT_MOUTH INT,
AMOUNT DECIMAL(18,2)
)

INSERT INTO SUMOVER VALUES ('牛奶','1','100')
INSERT INTO SUMOVER VALUES ('牛奶','2','200')
INSERT INTO SUMOVER VALUES ('牛奶','3','300')
INSERT INTO SUMOVER VALUES ('牛奶','4','400')
INSERT INTO SUMOVER VALUES ('牛奶','5','500')
INSERT INTO SUMOVER VALUES ('牛奶','6','600')
INSERT INTO SUMOVER VALUES ('牛奶','7','700')
INSERT INTO SUMOVER VALUES ('牛奶','8','800')
INSERT INTO SUMOVER VALUES ('牛奶','9','900')
INSERT INTO SUMOVER VALUES ('牛奶','10','1000')
INSERT INTO SUMOVER VALUES ('牛奶','11','1100')
INSERT INTO SUMOVER VALUES ('牛奶','12','1200')
INSERT INTO SUMOVER VALUES ('酱油','1','100')
INSERT INTO SUMOVER VALUES ('酱油','2','200')
INSERT INTO SUMOVER VALUES ('酱油','3','300')
INSERT INTO SUMOVER VALUES ('酱油','4','400')
INSERT INTO SUMOVER VALUES ('酱油','5','500')
INSERT INTO SUMOVER VALUES ('酱油','6','600')
INSERT INTO SUMOVER VALUES ('酱油','7','700')
INSERT INTO SUMOVER VALUES ('酱油','8','800')
INSERT INTO SUMOVER VALUES ('酱油','9','900')
INSERT INTO SUMOVER VALUES ('酱油','10','1000')
INSERT INTO SUMOVER VALUES ('酱油','11','1100')
INSERT INTO SUMOVER VALUES ('酱油','12','1200')

查询sql如下：

SELECT *,SUM(AMOUNT)OVER(PARTITION BY PRODUCT ORDER BY CONTRACT_MOUTH) AS SUM_AMOUNT
FROM SUMOVER

查询结果如下：