假设表temp
name workdate val
a 20060301 3
b 20060420 6
c 20060103 2
a 20060508 1
d 20060201 9
.......其中workdate是字符型表示的日期,val为数值,现在想求一sql语句实现如下结果:name month sumval(合计值)
a 200601 ?
a 200602 ?
a 200603 ?
b 200601 ?
....
即按name和月份分组求出val的合计值,但这个合计值的要求是如果月份是200602,那么求出200602月之前所有val的合计值,而不仅是2月份的合计值。
name workdate val
a 20060301 3
b 20060420 6
c 20060103 2
a 20060508 1
d 20060201 9
.......其中workdate是字符型表示的日期,val为数值,现在想求一sql语句实现如下结果:name month sumval(合计值)
a 200601 ?
a 200602 ?
a 200603 ?
b 200601 ?
....
即按name和月份分组求出val的合计值,但这个合计值的要求是如果月份是200602,那么求出200602月之前所有val的合计值,而不仅是2月份的合计值。
where .....
group by name, substr(month,1,6);我没试,建表麻烦,不过八九不离十了
substr(workdate,1,6) month,
SUM (val) OVER (PARTITION BY name ORDER BY substr(workdate,1,6) RANGE UNBOUNDED PRECEDING) sumval
from temp
select name,
month,
SUM (sval) OVER (PARTITION BY name ORDER BY month RANGE UNBOUNDED PRECEDING) sumval
from
(select name,substr(workdate,1,6) month, sum(val) sval
from sumval
group by name,substr(workdate,1,6))
from temp group by name,substr(workdate,0,6)
--- --------------- ----------
a 20060301 3
b 20060420 6
c 20060103 2
a 20060508 1
d 20060201 9
b 20060120 8
b 20060220 2
b 20060320 5SQL> select name , substr(workdate,1,6) as month,
2 sum(val) over(partition by name order by workdate
3 rows between unbounded preceding and 0 following) as sumval
4 from temp ;NAM MONTH SUMVAL
--- ------------ ----------
a 200603 3
a 200605 4
b 200601 8
b 200602 10
b 200603 15
b 200604 21
c 200601 2
d 200602 9已选择8行。