请教一个SQL语句,从A表得到B的结果
A:
id datetime num
-----------------------
1 20100501 3
1 20100502 5
1 20100503 4
2 20100501 6
2 20100502 7
2 20100503 5B:
id datetime sum
-----------------------
1 20100502 8
1 20100503 12
2 20100502 13
2 20100503 18统计指定的日期之前的num的和
A:
id datetime num
-----------------------
1 20100501 3
1 20100502 5
1 20100503 4
2 20100501 6
2 20100502 7
2 20100503 5B:
id datetime sum
-----------------------
1 20100502 8
1 20100503 12
2 20100502 13
2 20100503 18统计指定的日期之前的num的和
select 1 id,to_date('20100501','yyyymmdd') datetime,3 num from dual
union all
select 1 id,to_date('20100502','yyyymmdd') datetime,5 num from dual
union all
select 1 id,to_date('20100503','yyyymmdd') datetime,4 num from dual
union all
select 2 id,to_date('20100501','yyyymmdd') datetime,6 num from dual
union all
select 2 id,to_date('20100502','yyyymmdd') datetime,7 num from dual
union all
select 2 id,to_date('20100503','yyyymmdd') datetime,5 num from dual
)
select id,datetime, sumn from(
select id,datetime,sum(num) over(partition by id order by rn) sumn,rn from(
select id,datetime,num,row_number() over(partition by id order by id) rn from temp
)) where rn != 1
id datetime sum
-----------------------
1 20100502 8
1 20100503 12
2 20100502 13
2 20100503 18
2 20100504 18
2 20100505 18
注意,20100504的记录,在A中不存在。
也就是说,我要是查询不存在的日期的时候,sum的值为前面最近日期的最大值
从20100503往后的日期是连贯的
from
(
select id,datetime,sum(num) over(partition by id order by datetime) total,
row_number() over(partition by id order by datetime) rn
from A
)
where rn!=1