SUM OVER PARTITION BY ORDER BY(分组累计计算方法)

仅限SQL SERVER2012以上版本

create table TEST(
ID int identity(1,1),
NAME NVARCHAR(50),
GROUPNAME NVARCHAR(50),
GRADE INT,
DIRECTION INT
)

DROP TABLE TEST

INSERT INTO TEST(NAME,GROUPNAME,GRADE,DIRECTION)
SELECT '小明','开发部',90,-1 UNION ALL
SELECT '小张','开发部',89,+1 UNION ALL
SELECT '小白','开发部',100,-1 UNION ALL
SELECT '小王','财务部',70,-1 UNION ALL
SELECT '小赵','财务部',69,+1 UNION ALL
SELECT '小柳','财务部',90,+1 UNION ALL
SELECT '小高','行政部',78,-1 UNION ALL
SELECT '小王','行政部',77,+1 UNION ALL
SELECT '小李','行政部',99,-1 UNION ALL
SELECT '小吴','行政部',34,-1

SELECT * FROM TEST

SELECT  ID,
        NAME,
        GROUPNAME,
        GRADE,
        DIRECTION,
        SUM(GRADE*DIRECTION) OVER(PARTITION BY GROUPNAME ORDER BY ID) AS ACCUMULATION
FROM TEST