;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 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 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组合一起排序的,我想要的是可以直接sql中查询得到 语句能直接运行查询么
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
有一个表
a b c d
2201 20150101001 0001 10
2201 20150101001 0002 35
2201 20150101001 0003 45
2202 20150101001 0001 17
2202 20150101001 0002 56
2202 20150101001 0003 23
所得到的表
a b c d e
2201 20150101001 0001 10 10
2201 20150101001 0002 35 45
2201 20150101001 0003 45 90
2202 20150101001 0001 17 17
2202 20150101001 0002 56 73
2202 20150101001 0003 23 96
f列的数据为当a b 一致时c为序号,当前行的e=当前行的d加上一行的e
SELECT ROW_NUMBER()OVER(PARTITION BY a,b ORDER BY c)RN
,*
FROM TB
)
SELECT T1.a,T1.b,T1.c,T1.d,SUM(T2.d)E
FROM CTE T1
JOIN CTE T2 ON T1.a=T2.a AND T1.b=T2.b AND T1.RN>=T2.RN
GROUP BY T1.a,T1.b,T1.c,T1.d,T1.RN那你试试这个
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