有一个表
a b c d e
2201 20150101001 0001 11 10
2201 20150101001 0001 11 35
2201 20150101001 0001 11 45
2202 20150101001 0002 11 17
2202 20150101001 0002 11 56
2202 20150101001 0002 11 23
所得到的表
a b c d e f
2201 20150101001 0001 11 10 10
2201 20150101001 0001 11 35 45
2201 20150101001 0001 11 45 90
2202 20150101001 0002 11 17 17
2202 20150101001 0002 11 56 73
2202 20150101001 0002 11 23 96
f列的数据为当a b c d一致时，当前行的f=当前行的e加上一行的f
------解决思路----------------------
;WITH CTE AS(你这个累计是要求有顺序的，你却没有给这个顺序的列名
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN
,*
FROM TB
)
SELECT T1.a,T1.b,T1.c,T1.d,T1.e,SUM(T2.e)f
FROM CTE T1
JOIN CTE T2 ON T1.RN>=T2.RN
GROUP BY T1.a,T1.b,T1.c,T1.d,T1.e,T1.RN
------解决思路----------------------
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN
,*
FROM TB
)
SELECT T1.a,T1.b,T1.c,T1.d,T1.e,SUM(T2.e)f
INTO #临时表
FROM CTE T1
JOIN CTE T2 ON T1.RN>=T2.RN
AND T1.a = T2.a AND T1.b = T2.b
AND T1.c = T2.c AND T1.d = T2.d
GROUP BY T1.a,T1.b,T1.c,T1.d,T1.e,T1.RN
------解决思路----------------------
我说的顺序，是指，a b c d都相同的同组间的顺序方式，没有额外的列用来指定顺序吗
以下这个可以直接查询，不用插入到临时表
;WITH CTE AS(
SELECT ROW_NUMBER()OVER(ORDER BY GETDATE())RN
,*
FROM TB
)
SELECT T1.a,T1.b,T1.c,T1.d,T1.e,SUM(T2.e)f
FROM CTE T1
JOIN CTE T2 ON T1.RN>=T2.RN
GROUP BY T1.a,T1.b,T1.c,T1.d,T1.e,T1.RN
------解决思路----------------------
貌似可以直接这样
SELECT T1.a,T1.b,T1.c,T1.d,SUM(T2.d)E
FROM TB T1
JOIN TB T2 ON T1.a=T2.a AND T1.b=T2.b AND T1.c>=T2.c
GROUP BY T1.a,T1.b,T1.c,T1.d