比如有一个表
PNO SERIAL Score
A 1 30
A 2 100
A 3 150
A 4 50
A 7 120
A 8 200
A 12 100
B 1 200
B 2 200
想通过一个sql得到下面结果,按照PNO,汇总SERIAL连续的 SUM(Score)
PNO START END SUM(Score)
A 1 4 330
A 7 8 320
A 12 12 100
B 1 2 400想了好久没想出来。请求高人!
PNO SERIAL Score
A 1 30
A 2 100
A 3 150
A 4 50
A 7 120
A 8 200
A 12 100
B 1 200
B 2 200
想通过一个sql得到下面结果,按照PNO,汇总SERIAL连续的 SUM(Score)
PNO START END SUM(Score)
A 1 4 330
A 7 8 320
A 12 12 100
B 1 2 400想了好久没想出来。请求高人!
FROM (SELECT T1.PNO,
T1.SERIAL,
SUM(SCORE) OVER(PARTITION BY(SERIAL - ROWNUM)) SCORE,
SERIAL - ROWNUM TEMP
FROM (SELECT * FROM TT ORDER BY PNO, SERIAL) T1) T2
GROUP BY pno,temp,score
ORDER BY PNO;
强悍!!
这个都能想得出来
佩服啊佩服
漏了PARTITION BY pno了,加上,否则会出错:
SELECT pno,MIN(serial) "START",MAX(serial) END,score
FROM (SELECT T1.PNO,
T1.SERIAL,
SUM(SCORE) OVER(PARTITION BY pno, (SERIAL - ROWNUM)) SCORE,
SERIAL - ROWNUM TEMP
FROM (SELECT * FROM TT ORDER BY PNO, SERIAL) T1) T2
GROUP BY pno,temp,score
ORDER BY PNO;